Stored Procedure Parameters

Stored procedures can accept input parameters, return output parameters, and return values. This section details how to define and use parameters effectively in your SQL Server stored procedures.

Input Parameters

Input parameters allow you to pass values into a stored procedure. They are defined with the OUTPUT keyword omitted. If a parameter is optional, you can provide a default value.

Defining Input Parameters

Parameters are declared within the parentheses following the stored procedure name. Each parameter requires a name and a data type.

CREATE PROCEDURE usp_GetCustomerByID
                @CustomerID INT,
                @ActiveOnly BIT = 1 -- Optional parameter with a default value
            AS
            BEGIN
                SELECT CustomerID, CompanyName, ContactName
                FROM Customers
                WHERE CustomerID = @CustomerID
                AND ActiveStatus = @ActiveOnly;
            END;

Output Parameters

Output parameters allow a stored procedure to return data back to the caller. They are declared using the OUTPUT keyword.

Defining Output Parameters

An output parameter is declared with the OUTPUT keyword after its data type. The procedure must assign a value to this parameter before it completes execution.

CREATE PROCEDURE usp_GetCustomerCountByCity
                @CityName NVARCHAR(50),
                @CustomerCount INT OUTPUT
            AS
            BEGIN
                SELECT @CustomerCount = COUNT(*)
                FROM Customers
                WHERE City = @CityName;
            END;

Parameter Table

Here's a summary of common parameter attributes:

Attribute Description Example
@ParameterName The name of the parameter, typically prefixed with '@'. @CustomerID
DataType The data type of the parameter (e.g., INT, VARCHAR(50), DATETIME). INT
NULL/NOT NULL Specifies whether the parameter can accept NULL values. @CustomerID INT NULL
OUTPUT Indicates that the parameter is an output parameter. @CustomerCount INT OUTPUT
DEFAULT value Assigns a default value if no value is provided by the caller. @ActiveOnly BIT = 1

Executing Procedures with Parameters

When calling a stored procedure, you provide values for its parameters. For output parameters, you must declare a variable to receive the output value.

Example: Using Input and Output Parameters

-- Declare a variable to hold the output
DECLARE @CountOfCustomers INT;

-- Execute the stored procedure
EXEC usp_GetCustomerCountByCity
    @CityName = 'London',
    @CustomerCount = @CountOfCustomers OUTPUT;

-- Display the result
SELECT @CountOfCustomers AS NumberOfCustomersInLondon;

Parameter Direction

Parameters have a direction: INPUT (default), OUTPUT, INOUT (input and output), or RETURN (for return values, though typically handled separately).

Best Practices

Example: Parameter Validation

CREATE PROCEDURE usp_UpdateProductPrice
                    @ProductID INT,
                    @NewPrice DECIMAL(10, 2)
                AS
                BEGIN
                    IF NOT EXISTS (SELECT 1 FROM Products WHERE ProductID = @ProductID)
                    BEGIN
                        RAISERROR('Product with ID %d not found.', 16, 1, @ProductID);
                        RETURN; -- Exit the procedure
                    END

                    IF @NewPrice < 0
                    BEGIN
                        RAISERROR('Price cannot be negative.', 16, 1);
                        RETURN;
                    END

                    UPDATE Products
                    SET UnitPrice = @NewPrice
                    WHERE ProductID = @ProductID;

                    PRINT 'Product price updated successfully.';
                END;