Post
Ask Question

Return Value from EXEC Function in SQL Server

Stored Procedure that returns value:
The following Stored Procedure is used which returns an Integer value 1 if the StudentId exists and 0 if the StudentId does not exists.
  1. CREATE PROCEDURE CheckStudentId  
  2. @StudentId INT  
  3. AS  
  4. BEGIN  
  5. SET NOCOUNT ON;  
  6. DECLARE @Exists INT  
  7. IF EXISTS(SELECT StudentId FROM Students WHERE StudentId = @StudentId)  
  8. BEGIN  
  9. SET @Exists = 1  
  10. END  
  11. ELSE  
  12. BEGIN  
  13. SET @Exists = 0  
  14. END  
  15. RETURN @Exists  
  16. END  
Returned value from EXEC function:
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:
  1. DECLARE @ReturnValue INT  
  2. EXEC @ReturnValue = < Store Procedure Name > < Parameters > Select @ReturnValue  
  3. Example: DECLARE @ReturnValue INT  
  4. EXEC @ReturnValue = CheckStudentId 34  
  5. SELECT @ReturnValue  
If There are valid StudentId then Output will be : 1