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.
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
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
SET NOCOUNT ON;
DECLARE @Exists INT
IF EXISTS(SELECT EmployeeId
WHERE EmployeeId = @EmployeeId)
SET @Exists = 1
SET @Exists = 0
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.
DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckEmployeeId 1