In this article I will explain with an example, how to return result value of EXEC function and save it to a variable in SQL Server.
The capturing and assigning result value from EXEC function to a variable is supported in SQL Server versions i.e. 2000, 2005, 2008, 2008R2, 2012, 2014 or higher.
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
Stored Procedure that returns value
In order to illustrate the process of assigning result value EXEC function to Variable in SQL Server, the following Stored Procedure is used which returns an Integer value 1 if the EmployeeId exists and 0 if the EmployeeId does not exists.
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 EXEC function
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