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

CREATE PROCEDURE [dbo].[usp_GetAddrCount]
AS
BEGIN
DECLARE @Count INT ;
SET @Count = (SELECT COUNT(*) FROM [ADDRESS])
RETURN @Count
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:

DECLARE @return_value int
EXEC @return_value = dbo.GetAddrCount ;
IF(@return_value > 0)
BEGIN
SELECT COUNT(*) FROM [Booking]
END
ELSE
BEGIN
SELECT COUNT(*) FROM [Employee]
END

Example:

Here is the fill SQL Code:

CREATE PROCEDURE [dbo].[usp_GetAddrCount]
AS
BEGIN
DECLARE @Count INT ;
SET @Count = (SELECT COUNT(*) FROM [ADDRESS])
RETURN @Count
END

CREATE PROCEDURE [dbo].[usp_TakeDecision_BasedOnAddressCount]
AS
BEGIN
DECLARE @return_value int
EXEC @return_value = dbo.GetAddrCount ;
IF(@return_value > 0)
BEGIN
SELECT COUNT(*) FROM [Booking]
END
ELSE
BEGIN
SELECT COUNT(*) FROM [Employee]
END
END