Stored Procedure Parameters

Parameters allow you to pass values into a stored procedure, making it reusable and dynamic. A parameter can be defined as input, output, or both.

Syntax

CREATE PROCEDURE [ schema_name . ] procedure_name 
    [ @parameter_name data_type [ = default ] [ OUT | OUTPUT ] 
      [,...n] ]
AS
BEGIN
    -- Transact‑SQL statements
END

Parameter Types

Direction	Keyword	Description
Input	—	Default; value is passed from caller to procedure.
Output	OUTPUT / OUT	Procedure can modify the value and return it to the caller.
Input/Output	INOUT (via T‑SQL using default and OUTPUT)	Value is passed in and may be changed before being returned.

Examples

1. Simple Input Parameter

CREATE PROCEDURE dbo.GetEmployeeById 
    @EmpId INT
AS
BEGIN
    SELECT * FROM dbo.Employees WHERE EmployeeID = @EmpId;
END;

2. Parameter with Default Value

CREATE PROCEDURE dbo.GetOrders 
    @StartDate DATE = '19000101',
    @EndDate   DATE = GETDATE()
AS
BEGIN
    SELECT * FROM dbo.Orders 
    WHERE OrderDate BETWEEN @StartDate AND @EndDate;
END;

3. Output Parameter

CREATE PROCEDURE dbo.GetEmployeeCount 
    @DeptId INT,
    @Count  INT OUTPUT
AS
BEGIN
    SELECT @Count = COUNT(*) 
    FROM dbo.Employees 
    WHERE DepartmentID = @DeptId;
END;

4. Using Parameters from T‑SQL

DECLARE @EmployeeCount INT;
EXEC dbo.GetEmployeeCount @DeptId = 5, @Count = @EmployeeCount OUTPUT;
SELECT @EmployeeCount AS EmployeesInDept5;

Note: When using OUTPUT parameters, always specify the OUTPUT keyword both in the procedure definition and the EXEC statement.

Best Practices

Give parameters meaningful names prefixed with @.
Use default values for optional parameters.
Document each parameter with its purpose and expected data type.
Avoid using the SELECT * pattern; explicitly list columns.
When returning large result sets, consider using table‑valued parameters or OUTPUT parameters for scalar values only.