In this article I will explain with an example, how to return value True if record exists and value False when record does not exist from Stored Procedure in SQL Server.
In order to return value the EXEC function will be used. The value will be returned as 1 (True) if record exists and 0 (False) is record does not exists.
Using the EXEC function returning value of a Stored Procedure to a variable is supported in all SQL Server versions i.e. 2000, 2005, 2008, 2008R2, 2012, 2014 or higher.
Database
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
@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
![Return Value from Stored Procedure in SQL Server example]()
Example
DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckEmployeeId 1
SELECT @ReturnValue
Output
Valid EmployeeId
![Stored Procedure: Return True if record exists and False if record does not exist in SQL Server]()
Invalid EmployeeId
![Stored Procedure: Return True if record exists and False if record does not exist in SQL Server]()
Downloads
CheckEmployeeId.sql
