Stored Procedures: Output Parameters

Stored procedures, unlike functions, do not have to return any data back to the caller. They only execute the code that they encompass. Nevertheless, it is possible to return a result back from stored procedures. We can mark a parameter of a stored procedure with the OUTPUT keyword so that the value of the parameter can be used in the context where the stored procedure is called. In other words, using output parameters is one of the ways of returning data from stored procedures.

Syntax

Output parameters are declared in the same place where we have input parameters. The only difference is that they are marked with the OUTPUT keyword:

CREATE PROCEDURE Schema.ProcedureName
    @Parameter1 NVARCHAR(100),
    @Parameter2 INT OUTPUT
AS
BEGIN
    [Procedure body]
END;

In the code snippet above, @Parameter2 is an output parameter of type INT. To return a result, we simply assign this output parameter a value:

CREATE PROCEDURE Schema.ProcedureName
    @Parameter1 NVARCHAR(100),
    @Parameter2 INT OUTPUT
AS
BEGIN
    [Procedure body]
    SET @Parameter2 = 12
END;

Usage

To execute a stored procedure that requires an output parameter, we have to first create a variable of the same type as the output parameter and pass it along with the OUTPUT keyword:

DECLARE @VariableToHoldTheResult AS INT

EXEC Schema.ProcedureName 'Parameter1', @VariableToHoldTheResult OUTPUT

-- We can now use @VariableToHoldTheResult to view the result of the stored procedure

When we pass an output parameter, we are basically providing the stored procedure a place where it can store the result.

Example

Let’s say we have a table called Products:

CREATE TABLE dbo.Products
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Name NVARCHAR(100) NOT NULL,
    Price INT NOT NULL,
    QuantityLeft INT NOT NULL
);

We want to create a stored procedure that will calculate the total price of inventory that we have in our warehouse. To do that, we multiply Price by QuantityLeft for each product in our table. We must also return this result through an output parameter.

First, let’s create the base structure of our procedure:

CREATE PROCEDURE dbo.CalculateInventoryPrice
    @TotalPrice INT OUTPUT
AS
BEGIN
    [Procedure body]
END

The procedure will write the result to @TotalPrice. All we have to do now is to calculate the total price of inventory and assign this result to the output parameter: