Assign the result value of EXEC function to Variable in SQL Server

Introduction

 This article explains how to assign (set) result value of EXEC function to 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.

 Database

Here I am making use of Microsoft’s Northwind Database. In order to download the Microsoft’s free sample Northwind database, you will need to visit the following URL.

Download and install Northwind Database

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
      @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 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. 

Syntax 

Example

DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckEmployeeId 1
SELECT @ReturnValue

OutputValid EmployeeId 

 Invalid EmployeeId