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.
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
Example
DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckEmployeeId 1
SELECT @ReturnValue
Output
Valid EmployeeId
Invalid EmployeeId
Downloads
CheckEmployeeId.sql