Stored procedure output parameters, return values and result sets

A SQL Server stored procedure can hand data back to its caller in three ways: result sets, OUTPUT parameters and a return code. They aren’t interchangeable. Each has a job, and each has a trap that has caught most of us at least once.

The examples run against the SpDevDemo database.

Result sets: for rows

Every SELECT that returns rows to the client, rather than assigning variables, produces a result set:

CREATE OR ALTER PROCEDURE dbo.OrderDetails @OrderId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, OrderNumber, Status, Total            -- result set 1: the order
    FROM dbo.Orders WHERE OrderId = @OrderId;

    SELECT l.ProductId, p.Name, l.Quantity, l.UnitPrice   -- result set 2: its lines
    FROM dbo.OrderLines AS l
    JOIN dbo.Products AS p ON p.ProductId = l.ProductId
    WHERE l.OrderId = @OrderId;
END
GO

EXEC dbo.OrderDetails @OrderId = 7;

A procedure can return any number of result sets, of different shapes. The client reads them in order: in ADO.NET with reader.NextResult(), in Dapper with QueryMultiple.

Traps:

• Accidental result sets. A debugging SELECT left in the code becomes an extra result set, and the application reads the wrong one. So do the “(n rows affected)” messages for some older clients: always SET NOCOUNT ON.
• Conditional shapes. A procedure that returns different columns depending on a parameter is hard for callers to consume. Keep each result set’s shape fixed, and use WITH RESULT SETS at the call if you need to check it.

OUTPUT parameters: for single values

Use output parameters for a few scalar values: a new ID, a count, a computed total.

CREATE OR ALTER PROCEDURE dbo.CreateOrder
    @CustomerId  INT,
    @OrderId     INT          OUTPUT,
    @OrderNumber VARCHAR(20)  OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    SET @OrderNumber = CONCAT('SO-N', NEXT VALUE FOR dbo.OrderNumbers);

    INSERT dbo.Orders (CustomerId, OrderNumber, Status, OrderDate, Total)
    VALUES (@CustomerId, @OrderNumber, 'Open', CAST(SYSDATETIME() AS date), 0);

    SET @OrderId = SCOPE_IDENTITY();
END
GO

The procedure uses a sequence for the order numbers. Create it, then call the procedure:

CREATE SEQUENCE dbo.OrderNumbers AS INT START WITH 1;
GO

DECLARE @id INT, @number VARCHAR(20);

EXEC dbo.CreateOrder @CustomerId = 42, @OrderId = @id OUTPUT, @OrderNumber = @number OUTPUT;

SELECT @id AS NewOrderId, @number AS NewOrderNumber;

NewOrderId  NewOrderNumber
----------  --------------
200001      SO-N1

The procedure was created before the sequence existed, and that worked: SQL Server resolves the names of tables and sequences when a statement first runs, not when the procedure is created. It’s convenient, and also why a typo in a table name only shows up at run time.

Traps:

• Forgetting OUTPUT at the call. EXEC dbo.CreateOrder @CustomerId = 42, @OrderId = @id runs fine and leaves @id NULL. The keyword is needed on both sides.
• Output parameters are also inputs. Whatever value the caller’s variable holds is passed in. A procedure that reads an output parameter before setting it gets the caller’s value, not NULL.
• Errors. If the procedure fails partway, output parameters may hold partial values. Check for an error before using them.
• SCOPE_IDENTITY(), not @@IDENTITY. @@IDENTITY returns the last identity created in the session, including one inserted by a trigger. SCOPE_IDENTITY() returns the one from your statement’s scope. For several rows, use OUTPUT inserted.OrderId INTO @ids.

The return code: for status

RETURN n ends the procedure and hands the caller an integer:

CREATE OR ALTER PROCEDURE dbo.ShipOrder @OrderId INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Status VARCHAR(20) = (SELECT Status FROM dbo.Orders WHERE OrderId = @OrderId);

    IF @Status IS NULL      RETURN 1;     -- no such order
    IF @Status <> 'Open'    RETURN 2;     -- wrong state

    UPDATE dbo.Orders SET Status = 'Shipped' WHERE OrderId = @OrderId;
    RETURN 0;
END
GO

DECLARE @rc INT;
EXEC @rc = dbo.ShipOrder @OrderId = 1;
SELECT @rc AS ReturnCode;    -- 2: order 1 is not open

Traps:

• Integers only. RETURN can’t return a string or a decimal; use an output parameter.
• NULL becomes 0. Returning a NULL variable prints The ‘ShipOrder’ procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. and the caller sees success.
• Nobody checks it. A return code is easy to ignore, and a failure that’s ignored is worse than an error. For real failures, raise an error with THROW. Keep return codes for expected outcomes the caller should branch on, like “already shipped”.
• Errors don’t set it the way you’d expect. When a statement fails with an error, the procedure may carry on and RETURN 0. The return code doesn’t tell you an error happened.

Which one to use

You want to return	Use
Rows	A result set
Several result tables (an order and its lines)	Several result sets, in a fixed order
A new ID, a count, a computed value	An OUTPUT parameter
Many IDs from a multi-row insert	A result set from OUTPUT inserted...
An expected outcome the caller branches on	The return code
A failure	An error, with THROW

Reading them from .NET

ADO.NET reads output parameters and the return code only after the last result set has been read and the reader closed. A common bug is checking parameter.Value while the SqlDataReader is still open and getting null. The C# article has working code for ADO.NET, Dapper and EF Core.

Seeing them while you debug

In SSMS, the return code shows as Return Value only when you use Execute Stored Procedure… from Object Explorer. In SpDev, stepping past the last statement stops once more on END, with every variable and output parameter shown in the Variables view and the last result set available on hover, so you can check what the procedure is about to hand back before it does.