In this article I will explain with an example how to return value from a Stored Procedure in SQL Server. SQL Server allows to return a single integer value from a Stored Procedure using the RETURN keyword.
The Return keyword is supported in Stored Procedures of all SQL Server versions i.e. 2000, 2005, 2008, 2008R2, 2012 and 2014.
 
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
Download and install Northwind Database
 
Using Return keyword in Stored Procedure
The following Stored Procedure accepts EmployeeId as parameter. It checks whether an Employee with the supplied EmployeeId exists in the Employees table of the Northwind database.
Note: A Stored Procedure can return only INTEGER values. You cannot use it for returning values of any other data types.
If the Employee exists it returns value 1 and if the EmployeeId is not valid then it returns 0.
CREATE PROCEDURE CheckEmployeeId
      @EmployeeId INT
AS
BEGIN
      SET NOCOUNT ON;
 
      DECLARE @Exists INT
 
      IF EXISTS(SELECT EmployeeId
                        FROM Employees
                        WHERE EmployeeId = @EmployeeId)
      BEGIN
            SET @Exists = 1
      END
      ELSE
      BEGIN
            SET @Exists = 0
      END
 
      RETURN @Exists
END
 
 
 
Fetching returned value from Stored Procedure
In order to fetch the returned integer value from the Stored Procedure, you need to make use of an Integer variable and use along with the EXEC command while executing the Stored Procedure.
Syntax
Return Value from Stored Procedure in SQL Server example
Example
DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckEmployeeId 1
SELECT @ReturnValue
 
Output
Valid EmployeeId
Return Value from Stored Procedure in SQL Server example
Invalid EmployeeId
Return Value from Stored Procedure in SQL Server example
 
Downloads

CheckEmployeeId.sql