SQL Server Interview Question #110

What is a return value from a stored procedure?

Views, Stored Procedures & User-Defined Functions Mid-Level Intermediate

Detailed Explanation

A stored procedure can return an integer status value using RETURN. This is different from returning rows with SELECT and different from OUTPUT parameters.

RETURN is conventionally used for status or result codes rather than returning business data. For example, zero can represent success while another integer represents a particular condition. Applications should not confuse the procedure return code with a SELECT result set.

Code Example

CREATE PROCEDURE dbo.CheckProduct
    @Id INT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM dbo.Products WHERE Id = @Id)
        RETURN 0;

    RETURN 1;
END;
GO

DECLARE @Result INT;
EXEC @Result = dbo.CheckProduct @Id = 10;
SELECT @Result AS ReturnCode;