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]
  3. AS
  4. BEGIN
  6. DECLARE @Count INT ;
  8. SET @Count = (SELECT COUNT(*) FROM [ADDRESS])
  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
  3. EXEC @return_value = dbo.GetAddrCount ;
  5. IF(@return_value > 0)
  7. BEGIN
  8. SELECT COUNT(*) FROM [Booking]
  10. END
  11. ELSE
  12. BEGIN
  14. SELECT COUNT(*) FROM [Employee]
  15. END
Example:

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