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