Post
Ask Question

Use Return Value from a Stored Procedure which Executed within Another Stored Procedure

Here is the simple way to do so:

Step 1:

Create a stored procedure (i.e. usp_GetAddrCount) in such a way that it returns a value to the caller stored procedure (i.e. usp_TakeDecision_BasedOnAddressCount).

  1. CREATE PROCEDURE [dbo].[usp_GetAddrCount]   
  2.   
  3. AS  
  4. BEGIN  
  5.   
  6. DECLARE @Count INT ;  
  7.   
  8. SET @Count = (SELECT COUNT(*) FROM [ADDRESS])  
  9.   
  10. RETURN @Count  
  11. END  
Step 2:

In the stored Procedure (i.e. usp_TakeDecision_BasedOnAddressCount) which calls another Stored Procedure (i.e. usp_GetAddrCount), you need to declare a variable which holds the returned value from the called stored procedure and execute the store procedure within a stored procedire as following:
  1. DECLARE @return_value int  
  2.   
  3. EXEC @return_value = dbo.GetAddrCount ;  
  4.   
  5. IF(@return_value > 0)  
  6.   
  7. BEGIN  
  8. SELECT COUNT(*) FROM [Booking]  
  9.   
  10. END  
  11. ELSE  
  12. BEGIN  
  13.   
  14. SELECT COUNT(*) FROM [Employee]  
  15. END  
Example:

Here is the fill SQL Code:
  1. CREATE PROCEDURE [dbo].[usp_GetAddrCount]   
  2.    
  3. AS  
  4. BEGIN  
  5.       
  6. DECLARE @Count INT ;  
  7.   
  8.     SET @Count = (SELECT COUNT(*) FROM [ADDRESS])  
  9.       
  10.     RETURN @Count  
  11. END  
  1. CREATE PROCEDURE [dbo].[usp_TakeDecision_BasedOnAddressCount]   
  2.   
  3. AS  
  4. BEGIN  
  5.   
  6. DECLARE @return_value int  
  7.   
  8.     EXEC  @return_value  = dbo.GetAddrCount ;  
  9.       
  10.     IF(@return_value > 0)  
  11.         BEGIN  
  12.             SELECT COUNT(*) FROM [Booking]  
  13.   
  14.         END  
  15.     ELSE  
  16.         BEGIN  
  17.               
  18.             SELECT COUNT(*)  FROM [Employee]  
  19.         END  
  20. END  

 
Next Recommended Reading Executing SQL Stored Procedure from Another Stored Procedure