SQL Topics
Execute Procedure
title: Execute Procedure
In the previous lessons, you learned:
What Stored Procedures Are
How To Create Procedures
How To Use ParametersCreating a Stored Procedure is only the first step.
To actually use the procedure, we must execute it.
Execution means:
Run Procedure
Execute SQL Statements
Return ResultsWhenever a procedure is executed, the database processes the SQL statements stored inside the procedure.
Stored Procedures can be executed:
Without Parameters
With Input Parameters
With Output Parameters
With Multiple ParametersUnderstanding procedure execution is essential because enterprise applications call stored procedures thousands or even millions of times every day.
What Does Execute Procedure Mean?
Executing a procedure means:
Calling A Stored Procedureand asking the database to run the SQL statements stored inside it.
Think of it like:
Create Procedure
↓
Store Procedure
↓
Execute Procedure
↓
Get ResultsWhy is Procedure Execution Important?
Suppose a procedure exists:
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM Employees;
END;Creating the procedure only stores it.
To retrieve employee data:
Procedure Must Be ExecutedBasic Execution Syntax
SQL Server:
EXEC ProcedureName;or
EXECUTE ProcedureName;Both are valid.
Example
Procedure:
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM Employees;
END;Execution:
EXEC GetEmployees;Result:
Employee Records ReturnedCreating Sample Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Insert sample records:
INSERT INTO Employees VALUES
(1,'Rahul',50000),
(2,'Priya',60000),
(3,'Amit',70000);Executing a Procedure Without Parameters
Procedure:
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM Employees;
END;Execution:
EXEC GetEmployees;Output:
| EmployeeID | EmployeeName | Salary |
|---|---|---|
| 1 | Rahul | 50000 |
| 2 | Priya | 60000 |
| 3 | Amit | 70000 |
Executing a Procedure With One Parameter
Procedure:
CREATE PROCEDURE GetEmployee
(
@EmployeeID INT
)
AS
BEGIN
SELECT *
FROM Employees
WHERE EmployeeID =
@EmployeeID;
END;Execution:
EXEC GetEmployee 1;Output:
| EmployeeID | EmployeeName | Salary |
|---|---|---|
| 1 | Rahul | 50000 |
Executing Using Named Parameters
Procedure:
EXEC GetEmployee
@EmployeeID = 2;Output:
| EmployeeID | EmployeeName | Salary |
|---|---|---|
| 2 | Priya | 60000 |
Named parameters improve readability.
Executing Procedure With Multiple Parameters
Procedure:
CREATE PROCEDURE GetEmployeesBySalary
(
@MinSalary DECIMAL(10,2),
@MaxSalary DECIMAL(10,2)
)
AS
BEGIN
SELECT *
FROM Employees
WHERE Salary BETWEEN
@MinSalary
AND
@MaxSalary;
END;Execution:
EXEC GetEmployeesBySalary
50000,
70000;Using Named Multiple Parameters
EXEC GetEmployeesBySalary
@MinSalary = 50000,
@MaxSalary = 70000;This method is easier to understand.
Executing String Parameters
Procedure:
CREATE PROCEDURE FindEmployee
(
@EmployeeName VARCHAR(100)
)
AS
BEGIN
SELECT *
FROM Employees
WHERE EmployeeName =
@EmployeeName;
END;Execution:
EXEC FindEmployee
'Rahul';Result:
Employee Information ReturnedExecuting Date Parameters
Procedure:
CREATE PROCEDURE GetOrders
(
@OrderDate DATE
)
AS
BEGIN
SELECT *
FROM Orders
WHERE OrderDate =
@OrderDate;
END;Execution:
EXEC GetOrders
'2025-01-01';Executing Output Parameters
Procedure:
CREATE PROCEDURE EmployeeCount
(
@TotalEmployees INT OUTPUT
)
AS
BEGIN
SELECT
@TotalEmployees =
COUNT(*)
FROM Employees;
END;Calling Output Parameter Procedure
DECLARE @Count INT;
EXEC EmployeeCount
@Count OUTPUT;
SELECT @Count;Result:
3The procedure returns the employee count.
Executing Procedures in MySQL
MySQL syntax:
CALL GetEmployees();Procedure execution uses:
CALLinstead of:
EXECExecuting Procedures in SQL Server
SQL Server syntax:
EXEC GetEmployees;or
EXECUTE GetEmployees;Executing Procedures in PostgreSQL
PostgreSQL syntax:
CALL GetEmployees();Executing Procedures from Applications
Applications frequently execute procedures.
Example:
Java
Python
Node.js
PHP
C#Applications send:
Procedure Name
+
Parametersto the database.
Banking Example
Procedure:
Get Account DetailsExecution:
EXEC GetAccount
1001;Returns:
Account InformationE-Commerce Example
Procedure:
Get Product InformationExecution:
EXEC GetProduct
501;Returns:
Product DetailsPayroll Example
Procedure:
Calculate SalaryExecution:
EXEC CalculateSalary
101,
2025,
1;Returns payroll information.
Hospital Example
Procedure:
Get Patient InformationExecution:
EXEC GetPatient
5001;Returns patient details.
Benefits of Executing Procedures
Reusability
Same procedure used repeatedly.
Better Performance
Stored procedures are often precompiled.
Improved Security
Users execute procedures instead of accessing tables directly.
Centralized Logic
Business rules stay inside the database.
Reduced Network Traffic
Only procedure calls are transmitted.
Common Errors
Procedure Does Not Exist
Example:
EXEC UnknownProcedure;Result:
Procedure Not FoundMissing Required Parameters
Example:
EXEC GetEmployee;Result:
Parameter Missing ErrorWrong Data Type
Example:
EXEC GetEmployee 'ABC';when INT is expected.
Forgetting OUTPUT Keyword
Output values may not be returned correctly.
Best Practices
Use Named Parameters
Improves readability.
Validate Inputs
Check parameter values.
Handle Errors Properly
Use exception handling.
Test Procedures Thoroughly
Verify expected results.
Document Procedure Usage
Explain parameters and outputs.
Common Interview Questions
How do you execute a Stored Procedure?
Using:
EXEC ProcedureName;or:
CALL ProcedureName();depending on the database system.
What is the difference between EXEC and CALL?
SQL Server uses:
EXECMySQL and PostgreSQL commonly use:
CALLCan procedures accept parameters?
Yes.
Input and output parameters are supported.
Can procedures return values?
Yes.
Using output parameters or result sets.
Why are Stored Procedures executed instead of writing queries repeatedly?
To improve reusability, security, and maintainability.
Summary
Executing Stored Procedures allows applications and users to run reusable SQL logic stored inside the database. Procedures can accept parameters, return values, and perform complex operations efficiently and securely.
In this lesson, you learned:
- What procedure execution means
- EXEC syntax
- CALL syntax
- Executing procedures with parameters
- Output parameters
- Database-specific execution methods
- Real-world examples
- Benefits and limitations
- Best practices
Mastering procedure execution is essential because every stored procedure must ultimately be executed to perform useful work.
Next Step
Continue to the next lesson:
Procedure vs Function →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Execute Procedure.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, stored, procedures
Related SQL Topics