SQL Notes
Learn SQL Procedure Parameters in detail, understand input parameters, output parameters, parameterized procedures, and how to pass values dynamically.
A stored procedure without parameters is like a vending machine with only one button — it does the same thing every time. Parameters transform procedures from rigid, single-purpose blocks into flexible, reusable tools that can handle different inputs and return different outputs based on what you pass to them.
When you write application code, functions accept arguments. Procedure parameters serve the same purpose in SQL — they let you pass data into the procedure at runtime so the same logic can process different values each time it executes. This is fundamental to writing maintainable database code because it eliminates the need for dozens of nearly-identical procedures that differ only in their hardcoded values.
Parameters are variables declared in the procedure definition that receive values when you execute the procedure. They allow the same procedure to work with different data each time it runs, making your database code dramatically more powerful and maintainable.
Types of Parameters
SQL supports three types of procedure parameters:
| Type | Direction | Purpose |
|---|---|---|
| IN (Input) | Caller to Procedure | Pass values into the procedure |
| OUT (Output) | Procedure to Caller | Return values back to the caller |
| INOUT | Both directions | Pass in a value, modify it, return it |
Input Parameters (IN)
Input parameters receive values from the caller. They are the most common type.
In SQL Server, input parameters are declared with the @ prefix before the parameter name, followed by the data type. In MySQL, you explicitly mark them with the IN keyword. Both approaches achieve the same result — the parameter receives a value from the caller and makes it available inside the procedure body for use in queries, conditions, and calculations.
SQL Server Syntax
CREATE PROCEDURE GetEmployeesByDept
@DeptID INT
AS
BEGIN
SELECT EmpID, Name, Salary
FROM Employees
WHERE DepartmentID = @DeptID;
END;MySQL Syntax
DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN dept_id INT)
BEGIN
SELECT EmpID, Name, Salary
FROM Employees
WHERE DepartmentID = dept_id;
END //
DELIMITER ;Calling with Input Parameters
-- SQL Server
EXEC GetEmployeesByDept @DeptID = 3;
-- MySQL
CALL GetEmployeesByDept(3);Multiple Input Parameters
Procedures can accept multiple parameters for more specific queries:
Having multiple parameters is extremely common in real applications. Think about a search form on a website where users can filter by department, salary range, location, and experience. Each filter becomes a parameter in the stored procedure. The procedure uses these parameters in its WHERE clause to dynamically build the result set based on what the user selected.
-- SQL Server
CREATE PROCEDURE SearchEmployees
@Department VARCHAR(50),
@MinSalary DECIMAL(10,2),
@MaxSalary DECIMAL(10,2)
AS
BEGIN
SELECT EmpID, Name, Department, Salary
FROM Employees
WHERE Department = @Department
AND Salary BETWEEN @MinSalary AND @MaxSalary
ORDER BY Salary DESC;
END;-- Execute with multiple parameters
EXEC SearchEmployees
@Department = 'Engineering',
@MinSalary = 50000,
@MaxSalary = 100000;MySQL Version
DELIMITER //
CREATE PROCEDURE SearchEmployees(
IN p_department VARCHAR(50),
IN p_min_salary DECIMAL(10,2),
IN p_max_salary DECIMAL(10,2)
)
BEGIN
SELECT EmpID, Name, Department, Salary
FROM Employees
WHERE Department = p_department
AND Salary BETWEEN p_min_salary AND p_max_salary
ORDER BY Salary DESC;
END //
DELIMITER ;CALL SearchEmployees('Engineering', 50000, 100000);Output Parameters (OUT)
Output parameters return values from the procedure back to the caller. They are useful when you need to get a computed result.
Output parameters work in the opposite direction of input parameters. Instead of passing a value in, the procedure calculates or retrieves a value and passes it back to the caller. This is particularly useful when you need to return a single computed value like a count, total, or status code without returning an entire result set. Think of output parameters as the procedure's way of whispering back a specific answer to the caller.
SQL Server
CREATE PROCEDURE GetEmployeeCount
@DeptID INT,
@EmpCount INT OUTPUT
AS
BEGIN
SELECT @EmpCount = COUNT(*)
FROM Employees
WHERE DepartmentID = @DeptID;
END;-- Calling with output parameter
DECLARE @Count INT;
EXEC GetEmployeeCount @DeptID = 3, @EmpCount = @Count OUTPUT;
PRINT 'Employee Count: ' + CAST(@Count AS VARCHAR);MySQL
DELIMITER //
CREATE PROCEDURE GetEmployeeCount(
IN dept_id INT,
OUT emp_count INT
)
BEGIN
SELECT COUNT(*) INTO emp_count
FROM Employees
WHERE DepartmentID = dept_id;
END //
DELIMITER ;CALL GetEmployeeCount(3, @count);
SELECT @count AS EmployeeCount;INOUT Parameters
INOUT parameters pass a value in, allow the procedure to modify it, and return the modified value:
INOUT parameters combine both directions into one. You give the procedure a starting value, it transforms that value according to its logic, and you get the modified result back in the same variable. This is less common than separate IN and OUT parameters, but useful for operations like applying a discount to a price, incrementing a counter, or transforming a string. The original value is overwritten with the new computed value.
MySQL Example
DELIMITER //
CREATE PROCEDURE ApplyDiscount(
INOUT price DECIMAL(10,2),
IN discount_percent INT
)
BEGIN
SET price = price - (price * discount_percent / 100);
END //
DELIMITER ;SET @product_price = 1000.00;
CALL ApplyDiscount(@product_price, 20);
SELECT @product_price; -- Returns 800.00Default Parameter Values
In SQL Server, you can provide default values for parameters, making them optional:
Default parameter values are a powerful feature that makes procedures more flexible without forcing callers to always provide every argument. When a parameter has a default, the caller can skip it entirely, and the procedure uses the predefined value. This is ideal for search procedures where most filters are optional — you want the procedure to work whether the user specifies one filter or all five. It mirrors how optional function arguments work in programming languages like Python or JavaScript.
CREATE PROCEDURE GetEmployees
@Department VARCHAR(50) = NULL,
@Status VARCHAR(20) = 'Active',
@Limit INT = 100
AS
BEGIN
SELECT TOP (@Limit) EmpID, Name, Department, Status
FROM Employees
WHERE (@Department IS NULL OR Department = @Department)
AND Status = @Status
ORDER BY Name;
END;-- Uses all defaults
EXEC GetEmployees;
-- Overrides department, uses other defaults
EXEC GetEmployees @Department = 'Sales';
-- Overrides all parameters
EXEC GetEmployees @Department = 'Engineering', @Status = 'Active', @Limit = 50;Real-World Example: E-Commerce Order Processing
CREATE PROCEDURE PlaceOrder
@CustomerID INT,
@ProductID INT,
@Quantity INT,
@OrderID INT OUTPUT,
@TotalPrice DECIMAL(10,2) OUTPUT
AS
BEGIN
DECLARE @UnitPrice DECIMAL(10,2);
DECLARE @Stock INT;
-- Get product price and stock
SELECT @UnitPrice = Price, @Stock = Stock
FROM Products WHERE ProductID = @ProductID;
-- Check stock availability
IF @Stock < @Quantity
BEGIN
RAISERROR('Insufficient stock', 16, 1);
RETURN;
END
-- Calculate total
SET @TotalPrice = @UnitPrice * @Quantity;
-- Create order
INSERT INTO Orders (CustomerID, ProductID, Quantity, TotalAmount, OrderDate)
VALUES (@CustomerID, @ProductID, @Quantity, @TotalPrice, GETDATE());
SET @OrderID = SCOPE_IDENTITY();
-- Update stock
UPDATE Products SET Stock = Stock - @Quantity WHERE ProductID = @ProductID;
END;DECLARE @NewOrderID INT, @Total DECIMAL(10,2);
EXEC PlaceOrder
@CustomerID = 1,
@ProductID = 5,
@Quantity = 2,
@OrderID = @NewOrderID OUTPUT,
@TotalPrice = @Total OUTPUT;
SELECT @NewOrderID AS OrderID, @Total AS TotalPrice;Real-World Example: Student Grade Calculator
DELIMITER //
CREATE PROCEDURE CalculateGrade(
IN student_id INT,
IN course_id VARCHAR(10),
OUT final_grade VARCHAR(2),
OUT grade_points DECIMAL(3,1)
)
BEGIN
DECLARE avg_marks DECIMAL(5,2);
SELECT AVG(Marks) INTO avg_marks
FROM ExamResults
WHERE StudentID = student_id AND CourseID = course_id;
IF avg_marks >= 90 THEN
SET final_grade = 'A+'; SET grade_points = 10.0;
ELSEIF avg_marks >= 80 THEN
SET final_grade = 'A'; SET grade_points = 9.0;
ELSEIF avg_marks >= 70 THEN
SET final_grade = 'B+'; SET grade_points = 8.0;
ELSEIF avg_marks >= 60 THEN
SET final_grade = 'B'; SET grade_points = 7.0;
ELSEIF avg_marks >= 50 THEN
SET final_grade = 'C'; SET grade_points = 6.0;
ELSE
SET final_grade = 'F'; SET grade_points = 0.0;
END IF;
END //
DELIMITER ;Common Mistakes to Avoid
Mistake 1: Not validating input parameters. Always check if parameters have valid values before using them in queries. NULL or invalid values can cause unexpected behavior.
Mistake 2: Forgetting the OUTPUT keyword when calling. In SQL Server, you must specify OUTPUT both in the procedure definition and when calling it.
Mistake 3: Too many parameters. If a procedure needs more than 5-6 parameters, consider using a table type parameter or restructuring the logic.
Mistake 4: Not using default values for optional parameters. Default values make procedures more flexible and easier to call.
Best Practices
- Name parameters descriptively — @CustomerID is better than @c or @id
- Validate inputs before processing to prevent bad data
- Use default values for optional parameters
- Keep parameter count reasonable — under 6 is ideal
- Document expected ranges with comments in the procedure
- Use consistent naming — prefix with @ in SQL Server, p_ in MySQL
Parameters transform stored procedures from static code blocks into dynamic, reusable database tools that form the backbone of any serious application's data layer.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Parameters in Stored Procedures.
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, parameters
Related SQL Complete Guide Topics