Microsoft Learn

Returning Values from Stored Procedures in SQL Server

Stored procedures in SQL Server offer several ways to return data and status information to the caller. Understanding these methods is crucial for building efficient and interactive database applications.

1. RETURN Statement (Integer Status Codes)

The RETURN statement is primarily used to indicate the success or failure of a stored procedure's execution. It returns an integer value. By convention:

You can capture this return value using the @@ERROR system function or by declaring an output parameter of type INT.

Example: Using RETURN for Status


CREATE PROCEDURE usp_CheckDataCount
    @TableName NVARCHAR(128),
    @RowCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'SELECT @Count = COUNT(*) FROM ' + QUOTENAME(@TableName);

    BEGIN TRY
        EXEC sp_executesql @SQL, N'@Count INT OUTPUT', @Count = @RowCount OUTPUT;
        RETURN 0; -- Success
    END TRY
    BEGIN CATCH
        RETURN -1; -- Error indicator
    END CATCH
END;

To execute and check the return value:


DECLARE @TableCount INT;
DECLARE @Status INT;

EXEC @Status = usp_CheckDataCount @TableName = N'YourTable', @RowCount = @TableCount OUTPUT;

IF @Status = 0
    PRINT 'Procedure executed successfully. Row count: ' + CAST(@TableCount AS VARCHAR);
ELSE
    PRINT 'Procedure encountered an error.';

2. OUTPUT Parameters

OUTPUT parameters are a flexible way to return one or more scalar values from a stored procedure. These parameters are declared with the OUTPUT or OUT keyword and can hold any data type.

Example: Using OUTPUT Parameters


CREATE PROCEDURE usp_GetCustomerInfo
    @CustomerID INT,
    @CustomerName NVARCHAR(100) OUTPUT,
    @EmailAddress NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        @CustomerName = Name,
        @EmailAddress = Email
    FROM
        Customers
    WHERE
        CustomerID = @CustomerID;

    IF @@ROWCOUNT = 0
        RETURN 1; -- Customer not found indicator
    ELSE
        RETURN 0; -- Success
END;

To execute and retrieve output parameters:


DECLARE @CustName NVARCHAR(100);
DECLARE @CustEmail NVARCHAR(255);
DECLARE @ExecStatus INT;

EXEC @ExecStatus = usp_GetCustomerInfo
    @CustomerID = 123,
    @CustomerName = @CustName OUTPUT,
    @EmailAddress = @CustEmail OUTPUT;

IF @ExecStatus = 0
BEGIN
    PRINT 'Customer Name: ' + @CustName;
    PRINT 'Email: ' + @CustEmail;
END
ELSE
    PRINT 'Customer not found.';

3. Result Sets (SELECT Statements)

The most common way to return multiple rows and columns of data from a stored procedure is by using SELECT statements. A stored procedure can return multiple result sets, each generated by a separate SELECT statement.

Example: Returning Result Sets


CREATE PROCEDURE usp_GetProductDetails
    @ProductID INT
AS
BEGIN
    SET NOCOUNT ON;

    -- First result set: Product Information
    SELECT
        ProductID,
        ProductName,
        UnitPrice
    FROM
        Products
    WHERE
        ProductID = @ProductID;

    -- Second result set: Related Products
    SELECT TOP 5
        RelatedProductID,
        ProductName AS RelatedProductName
    FROM
        ProductRelationships pr
    JOIN
        Products p ON pr.RelatedProductID = p.ProductID
    WHERE
        pr.ProductID = @ProductID;
END;

When you execute this procedure, your application will receive two distinct result sets.

4. Table-Valued Parameters (TVPs)

While primarily used for passing table-like data into a stored procedure, TVPs can also be the source of data that is then returned as a result set.

Example: Returning Data Based on TVP Input


-- First, create a user-defined table type
CREATE TYPE dbo.ProductList AS TABLE
(
    ProductID INT
);
GO

-- Then, create the stored procedure
CREATE PROCEDURE usp_GetProductsFromList
    @ProductIDs dbo.ProductList READONLY
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        p.ProductID,
        p.ProductName,
        p.UnitPrice
    FROM
        Products p
    JOIN
        @ProductIDs tvp ON p.ProductID = tvp.ProductID;
END;
GO

The calling application would provide a table variable or a parameter with the ProductList type.

Best Practices: