MSDN Documentation

Understanding Parameters in SQL Server Stored Procedures

Stored procedures are a powerful feature in SQL Server that allows you to encapsulate a series of T-SQL statements into a single executable unit. Parameters are crucial for making stored procedures dynamic and reusable. They allow you to pass values into the procedure when it is executed, influencing its behavior and the results it returns.

Types of Parameters

SQL Server stored procedures support several types of parameters:

• Input Parameters: These are the most common type. They pass a value into the stored procedure. The procedure uses this value but cannot modify the original variable passed in.
• Output Parameters: These allow a stored procedure to return a single value back to the caller. The procedure assigns a value to the output parameter, which can then be accessed by the calling application or script.
• Input/Output Parameters: These parameters act as both input and output. The caller passes an initial value, the procedure can modify it, and the modified value is returned to the caller.

Defining Parameters in a Stored Procedure

Parameters are defined within the parentheses following the `CREATE PROCEDURE` or `ALTER PROCEDURE` statement. Each parameter declaration includes its name (prefixed with `@`), its data type, and optionally, whether it's an `OUTPUT` parameter.

Syntax Example:

CREATE PROCEDURE usp_GetEmployeeById
    @EmployeeID INT,
    @FirstName VARCHAR(50) OUTPUT
AS
BEGIN
    -- Select employee details
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE EmployeeID = @EmployeeID;

    -- Set the output parameter
    SELECT @FirstName = FirstName
    FROM Employees
    WHERE EmployeeID = @EmployeeID;
END
        

Calling Stored Procedures with Parameters

When calling a stored procedure, you provide values for its parameters. The syntax for passing parameters varies slightly depending on whether you are using positional notation or named notation.

Named Notation (Recommended):

This is generally preferred as it makes the code more readable and less prone to errors if the parameter order changes.

DECLARE @EmpId INT = 101;
DECLARE @FName VARCHAR(50);

EXEC usp_GetEmployeeById
    @EmployeeID = @EmpId,
    @FirstName = @FName OUTPUT;

PRINT 'First Name: ' + @FName;
        

Positional Notation:

Values are passed in the order they are defined in the procedure. This can be error-prone.

DECLARE @EmpId INT = 102;
DECLARE @FName VARCHAR(50);

EXEC usp_GetEmployeeById @EmpId, @FName OUTPUT;
        

Parameter Options

• Default Values: You can assign default values to parameters. If a value is not provided when calling the procedure, the default value will be used.
• NULL Values: Parameters can accept or return `NULL` values, provided the data type allows it.
• Data Type Matching: Ensure the data types of the values passed match the declared parameter data types or are implicitly convertible.

Table-Valued Parameters (TVPs)

For passing multiple rows of data into a stored procedure, Table-Valued Parameters offer a highly efficient solution. They allow you to pass a table variable to a stored procedure, treating it like a single parameter.

To use TVPs, you first need to define a user-defined table type:

CREATE TYPE EmployeeList AS TABLE (
    EmployeeID INT PRIMARY KEY,
    Status VARCHAR(20)
);
        

Then, you can define a stored procedure that accepts this table type as a parameter:

CREATE PROCEDURE usp_UpdateEmployeeStatus
    @Employees EmployeeList READONLY
AS
BEGIN
    UPDATE e
    SET e.Status = el.Status
    FROM Employees e
    JOIN @Employees el ON e.EmployeeID = el.EmployeeID;
END
        

When calling this procedure, you would declare a table variable of the defined type and populate it before execution.