SQL Notes
Learn how to execute Stored Procedures in SQL, understand execution syntax, parameterized procedure execution, output parameters, and real-world examples.
Creating a stored procedure is only half the story. A procedure sitting in your database does nothing until you explicitly call it. Executing a stored procedure tells the database engine to run all the SQL statements stored inside it, process the logic, and return results.
In real-world applications, procedures are executed thousands of times per day — every time a user logs in, places an order, or views their dashboard, one or more stored procedures are being called behind the scenes. Understanding the different ways to execute procedures is essential for any database developer.
Executing a procedure is conceptually similar to calling a function in programming. You provide the procedure name, pass any required arguments, and the database engine runs the stored SQL logic and returns results. However, unlike simple function calls, SQL procedure execution involves understanding different syntax across database platforms, handling output parameters, capturing return status codes, and managing errors that might occur during execution. Mastering these patterns is what separates a beginner from a competent database developer.
Executing with Input Parameters
When a procedure accepts parameters, you pass values during execution:
The way you pass parameters during execution depends on your database platform. SQL Server offers both named and positional parameter passing, giving you flexibility in how you call procedures. MySQL uses only positional parameters, meaning you must provide values in the exact order they were declared. Named parameters are generally preferred because they make code self-documenting — anyone reading the execution statement can immediately see which value corresponds to which parameter without checking the procedure definition.
SQL Server — Named Parameters
-- Named parameters (recommended - clearer and order-independent)
EXEC GetEmployeesByDept @DeptID = 3;
-- Multiple named parameters
EXEC SearchEmployees
@Department = 'Engineering',
@MinSalary = 40000,
@MaxSalary = 100000;SQL Server — Positional Parameters
-- Positional (must match declaration order)
EXEC SearchEmployees 'Engineering', 40000, 100000;MySQL
-- Positional parameters (MySQL does not support named parameter syntax)
CALL GetEmployeesByDept(3);
CALL SearchEmployees('Engineering', 40000, 100000);Named parameters are preferred in SQL Server because they make the code self-documenting and allow you to skip optional parameters.
Executing with Output Parameters
Output parameters require special handling since you need to declare variables to receive the returned values:
Working with output parameters requires a two-step process that differs from simple execution. First, you must declare local variables to hold the values that the procedure will return. Then, when calling the procedure, you specify which of your local variables should receive the output values. After execution completes, your local variables contain the computed results that the procedure populated. This pattern is essential for procedures that calculate totals, validate data, or look up information that the calling code needs for further processing.
SQL Server
-- Step 1: Declare variables to hold output
DECLARE @TotalCount INT;
DECLARE @AverageSalary DECIMAL(10,2);
-- Step 2: Execute with OUTPUT keyword
EXEC GetDeptStats
@DeptID = 3,
@EmpCount = @TotalCount OUTPUT,
@AvgSalary = @AverageSalary OUTPUT;
-- Step 3: Use the returned values
SELECT @TotalCount AS EmployeeCount, @AverageSalary AS AvgSalary;MySQL
-- Step 1: Call with user-defined variables
CALL GetDeptStats(3, @total_count, @avg_salary);
-- Step 2: Read the output values
SELECT @total_count AS EmployeeCount, @avg_salary AS AvgSalary;Executing with Default Parameters
When parameters have default values, you can skip them:
-- Procedure definition (SQL Server)
CREATE PROCEDURE GetEmployees
@Status VARCHAR(20) = 'Active',
@Department VARCHAR(50) = NULL,
@Limit INT = 50
AS
BEGIN
SELECT TOP (@Limit) * FROM Employees
WHERE Status = @Status
AND (@Department IS NULL OR Department = @Department);
END;-- Execute with all defaults
EXEC GetEmployees;
-- Override only specific parameters
EXEC GetEmployees @Department = 'Sales';
EXEC GetEmployees @Status = 'Inactive', @Limit = 10;Storing Results in a Table
You can capture a procedure's result set into a table or table variable:
Sometimes you need to further process the results of a procedure — filter them, join them with other data, or aggregate them differently. In SQL Server, you can insert a procedure's output directly into a temporary table or table variable, which then allows you to run additional queries against those results. This technique is commonly used in reporting scenarios where one procedure generates raw data and subsequent logic transforms it into the desired format.
SQL Server
-- Using a temporary table
CREATE TABLE #Results (
EmpID INT,
Name VARCHAR(100),
Department VARCHAR(50),
Salary DECIMAL(10,2)
);
INSERT INTO #Results
EXEC GetEmployeesByDept @DeptID = 3;
-- Now query the results
SELECT * FROM #Results WHERE Salary > 60000;
DROP TABLE #Results;MySQL
Unfortunately, MySQL does not support INSERT INTO ... CALL directly. You would typically use a temporary table approach or restructure as a function.
Executing Procedures in Application Code
Procedures are most commonly called from application code:
In production environments, stored procedures are rarely called manually from a query editor. Instead, they are invoked by application code written in languages like Python, Java, C#, or Node.js. The application establishes a database connection, prepares the procedure call with parameters, executes it, and processes the returned results. This separation of concerns — business logic in the database and user interface logic in the application — is a fundamental architectural pattern in enterprise software development.
From a Python Application
-- The application sends this SQL
EXEC PlaceOrder @CustomerID = 101, @ProductID = 5, @Quantity = 2;From a Java Application (JDBC concept)
-- Prepared statement pattern
{CALL PlaceOrder(?, ?, ?)}The key point is that the application does not write raw SQL queries — it calls procedures, keeping the SQL logic centralized in the database.
Real-World Example: User Login Process
CREATE PROCEDURE UserLogin
@Username VARCHAR(50),
@PasswordHash VARCHAR(256),
@Success BIT OUTPUT,
@UserID INT OUTPUT,
@FullName VARCHAR(100) OUTPUT
AS
BEGIN
SET @Success = 0;
SET @UserID = 0;
SET @FullName = '';
SELECT @UserID = UserID, @FullName = FullName
FROM Users
WHERE Username = @Username AND PasswordHash = @PasswordHash AND IsActive = 1;
IF @UserID > 0
BEGIN
SET @Success = 1;
-- Update last login timestamp
UPDATE Users SET LastLogin = GETDATE() WHERE UserID = @UserID;
-- Log the login
INSERT INTO LoginHistory (UserID, LoginTime, IPAddress)
VALUES (@UserID, GETDATE(), '192.168.1.1');
END
END;-- Execute the login procedure
DECLARE @IsSuccess BIT, @UID INT, @Name VARCHAR(100);
EXEC UserLogin
@Username = 'rahul_dev',
@PasswordHash = 'abc123hash',
@Success = @IsSuccess OUTPUT,
@UserID = @UID OUTPUT,
@FullName = @Name OUTPUT;
-- Check login result
IF @IsSuccess = 1
PRINT 'Welcome, ' + @Name;
ELSE
PRINT 'Invalid credentials';Real-World Example: Monthly Sales Report
DELIMITER //
CREATE PROCEDURE MonthlySalesReport(
IN report_month INT,
IN report_year INT
)
BEGIN
SELECT
p.ProductName,
SUM(oi.Quantity) AS TotalSold,
SUM(oi.Quantity * oi.UnitPrice) AS Revenue
FROM OrderItems oi
JOIN Orders o ON oi.OrderID = o.OrderID
JOIN Products p ON oi.ProductID = p.ProductID
WHERE MONTH(o.OrderDate) = report_month
AND YEAR(o.OrderDate) = report_year
GROUP BY p.ProductName
ORDER BY Revenue DESC;
END //
DELIMITER ;-- Get report for June 2026
CALL MonthlySalesReport(6, 2026);Error Handling During Execution
SQL Server — TRY CATCH
BEGIN TRY
EXEC TransferFunds @FromAccount = 1001, @ToAccount = 1002, @Amount = 5000;
PRINT 'Transfer successful';
END TRY
BEGIN CATCH
PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH;MySQL — DECLARE HANDLER
DELIMITER //
CREATE PROCEDURE SafeInsert(IN emp_name VARCHAR(100), IN emp_email VARCHAR(255))
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 'Error occurred during insert' AS Message;
END;
INSERT INTO Employees (Name, Email) VALUES (emp_name, emp_email);
SELECT 'Employee added successfully' AS Message;
END //
DELIMITER ;Checking Return Status
SQL Server procedures can return integer status codes:
CREATE PROCEDURE ValidateOrder
@OrderID INT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM Orders WHERE OrderID = @OrderID)
RETURN -1; -- Order not found
IF EXISTS (SELECT 1 FROM Orders WHERE OrderID = @OrderID AND Status = 'Cancelled')
RETURN -2; -- Order already cancelled
RETURN 0; -- Valid order
END;DECLARE @Result INT;
EXEC @Result = ValidateOrder @OrderID = 500;
IF @Result = 0
PRINT 'Order is valid';
ELSE IF @Result = -1
PRINT 'Order not found';
ELSE IF @Result = -2
PRINT 'Order is cancelled';Common Mistakes to Avoid
Mistake 1: Forgetting OUTPUT keyword on both sides. In SQL Server, you must write OUTPUT in the procedure definition AND when calling it.
Mistake 2: Wrong parameter order with positional arguments. When not using named parameters, values must exactly match the declaration order.
Mistake 3: Not checking return values. Always check output parameters and return status to verify the procedure succeeded.
Mistake 4: Calling procedures without proper permissions. Users need EXECUTE permission on the procedure. Check grants if execution fails.
Best Practices
- Use named parameters for clarity and maintainability
- Always handle errors with TRY-CATCH or exception handlers
- Check output parameters before using their values
- Use return codes to indicate success or failure conditions
- Grant EXECUTE permission only to authorized users and roles
- Log procedure calls for auditing and debugging purposes
Executing stored procedures is how your applications interact with database logic. Master the execution patterns for your specific database platform, and you will write cleaner, more secure, and more maintainable code.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Execute Stored Procedures in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, stored, procedures, execute
Related SQL Complete Guide Topics