An Output Parameter allows a stored procedure to pass a value back to the calling script or application. While a standard query returns a result set (a table), an output parameter returns a single specific value.

1. How to Create a Stored Procedure with OUTPUT

To define an output parameter, you use the OUTPUT (or OUT) keyword after the data type in the CREATE PROCEDURE statement.

Example: This procedure takes a CategoryID and returns the TotalProducts in that category.

CREATE PROCEDURE GetCategoryCount
    @CategoryID INT,           -- Input Parameter
    @ProductCount INT OUTPUT   -- Output Parameter
AS
BEGIN
    SELECT @ProductCount = COUNT(*) 
    FROM Products 
    WHERE CategoryID = @CategoryID;
END;

2. How to Execute and Capture the Value

To get the value out of the procedure, you must:

  • Declare a local variable to hold the result.
  • Execute the procedure using the OUTPUT keyword again.
  • Use the variable after the execution.

Example:

-- 1. Declare the variable to hold the output
DECLARE @TotalFound INT;

-- 2. Execute the procedure
EXEC GetCategoryCount 
    @CategoryID = 5, 
    @ProductCount = @TotalFound OUTPUT; -- You MUST include OUTPUT here

-- 3. Use the value
PRINT 'Total products in this category: ' + CAST(@TotalFound AS VARCHAR);

3. Multiple Output Parameters

A single procedure can have many output parameters. This is useful when you need to return multiple individual data points without generating a full table.

Example:

CREATE PROCEDURE GetCustomerStats
    @CustomerID INT,
    @LastOrderDate DATETIME OUTPUT,
    @TotalSpent MONEY OUTPUT
AS
BEGIN
    SELECT 
        @LastOrderDate = MAX(OrderDate),
        @TotalSpent = SUM(TotalAmount)
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;

4. OUTPUT vs. RETURN

It is common to confuse OUTPUT parameters with the RETURN statement.

Feature OUTPUT Parameter RETURN Statement
Data Type Any (INT, VARCHAR, etc.) Only INT
Quantity Multiple allowed Only one per procedure
Purpose Returning data/values Returning execution status (0 = success)

Example of multiple outputs:

CREATE PROCEDURE GetSalesStats
    @Year INT,
    @TotalSales MONEY OUTPUT,
    @OrderCount INT OUTPUT
AS
BEGIN
    SELECT @TotalSales = SUM(TotalAmount), 
           @OrderCount = COUNT(OrderID)
    FROM Sales
    WHERE YEAR(OrderDate) = @Year;
END;

5. Capturing Identity Values

One of the most common uses for output parameters is returning the ID of a row that was just created.

CREATE PROCEDURE AddCustomer
    @Name NVARCHAR(50),
    @NewID INT OUTPUT
AS
BEGIN
    INSERT INTO Customers (CustomerName) VALUES (@Name);
    SET @NewID = SCOPE_IDENTITY(); -- Captures the auto-increment ID
END;

Categorized in:

Stored Procedures,