Stored Procedure Parameters

Stored procedures can accept input parameters to allow for dynamic execution and flexible data retrieval. Parameters enable you to pass values into a stored procedure, which can then be used within the procedure's logic.

Types of Parameters

There are three types of parameters that can be used with stored procedures:

Defining Parameters

Parameters are declared in the CREATE PROCEDURE statement. Each parameter consists of a name, a data type, and optionally a default value or direction (IN, OUT, INOUT).

Syntax

CREATE PROCEDURE procedure_name
        (@parameter1 datatype [= default_value] [IN | OUT | INOUT],
         @parameter2 datatype [= default_value] [IN | OUT | INOUT],
         ...)
    AS
    BEGIN
        -- Procedure logic here
    END

Example: Input Parameter

This example demonstrates a stored procedure that accepts an input parameter to filter results.

CREATE PROCEDURE GetCustomerByID
        (@CustomerID INT)
    AS
    BEGIN
        SELECT CustomerID, CompanyName, ContactName
        FROM Customers
        WHERE CustomerID = @CustomerID;
    END;

To execute this procedure, you would pass a customer ID:

EXEC GetCustomerByID @CustomerID = 5;

Example: Output Parameter

This example shows a stored procedure that returns the total number of orders for a given customer using an output parameter.

CREATE PROCEDURE GetOrderCountForCustomer
        (@CustomerID INT,
         @OrderCount INT OUTPUT)
    AS
    BEGIN
        SELECT @OrderCount = COUNT(*)
        FROM Orders
        WHERE CustomerID = @CustomerID;
    END;

To execute this procedure and retrieve the output:

DECLARE @count INT;
    EXEC GetOrderCountForCustomer @CustomerID = 10, @OrderCount = @count OUTPUT;
    SELECT @count AS TotalOrders;

Example: Input/Output Parameter

This example illustrates an input/output parameter. The procedure takes a quantity, increases it by 10%, and returns the new quantity.

CREATE PROCEDURE AdjustQuantity
        (@Quantity INT INOUT)
    AS
    BEGIN
        SET @Quantity = @Quantity * 1.10;
    END;

Execution:

DECLARE @currentQuantity INT = 100;
    EXEC AdjustQuantity @Quantity = @currentQuantity OUTPUT;
    SELECT @currentQuantity AS UpdatedQuantity; -- Will output 110

Default Parameter Values

You can specify a default value for a parameter. If the caller does not provide a value for that parameter, the default value will be used.

CREATE PROCEDURE GetProductsByCategory
        (@CategoryName NVARCHAR(50) = 'Beverages')
    AS
    BEGIN
        SELECT ProductID, ProductName, UnitPrice
        FROM Products
        WHERE CategoryName = @CategoryName;
    END;

Executing without a parameter:

EXEC GetProductsByCategory; -- Retrieves products in the 'Beverages' category

Executing with a specific parameter:

EXEC GetProductsByCategory @CategoryName = 'Confections';

Parameter Arrays (Table-Valued Parameters)

For passing multiple rows of data into a stored procedure, T-SQL supports Table-Valued Parameters (TVPs). This is a highly efficient way to send a collection of rows from a client application to SQL Server without requiring multiple round trips or temporary tables.

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

CREATE TYPE OrderList AS TABLE (
        ProductID INT,
        Quantity INT,
        Discount REAL
    );

Then, you can create a stored procedure that accepts this type:

CREATE PROCEDURE AddBulkOrders
        (@Orders OrderList READONLY)
    AS
    BEGIN
        INSERT INTO Orders (CustomerID, OrderDate, RequiredDate, ShipDate, OrderStatus, OrderQuantity)
        SELECT CustomerID, GETDATE(), GETDATE() + 5, NULL, 1, Quantity
        FROM @Orders;
        -- Further logic to process or calculate based on @Orders
    END;

Client applications would then create a table variable of type OrderList, populate it, and pass it to the procedure.

Best Practices