how to Create A SP with table variable and execute 3 SP statements in the same statement

Question

how to Create A SP with table variable and execute 3 SP statements in the same statement .

exec [dbo].[sp_KPI_Churn_MTD_Current_Year] exec [dbo].[sp_KPI_Churn_MTD_Previous_Year] exec [dbo].[sp_KPI_Churn_MTD_Less_Than_Previous_Year]

Results should be as in the picture Please see Image:

Answer 1

You could use a table variable with a structure similar to the SELECT from the 3 procedures, insert into it and do the math:

CREATE PROC ... AS
BEGIN
    DECLARE @data TABLE(Current_Churn_MTD int, Current_Goal_MTD int, Caption_MTD char(3));

    INSERT INTO @data
    EXEC [dbo].[sp_KTI_Churn_Current_Year];

    INSERT INTO @data
    EXEC [dbo].[sp_KTI_Churn_Previous_Year];

    INSERT INTO @data
    EXEC [dbo].[sp_KTI_Churn_Less_Than_Previous_Year];

    SELECT 'MTD' as Total
        , SUM(Current_Churn_MTD) as Current_Churn
        , SUM(Current_Goal_MTD) as Goal_Churn
    FROM @data
    ;
END

Output:

Total | Current_Churn | Goal_Churn
MTD   | 137           | 114

You could also change your Procedure to only return the 2 OUTPUT values:

ALTER PROC ... AS 
    Current_Churn_MTD int OUTPUT
    , @ Current_Goal_MTD OUTPUT
BEGIN 
...
END

And use this code instead in the new Procedure:

DECLARE @Previous_Churn_MTD int, @Previous_Goal_MTD int;
EXEC [dbo].[sp_KTI_Churn_Less_Than_Previous_Year] @Previous_Churn_MTD = @Previous_Churn_MTD OUTPUT, @Current_Goal_MTD = @Previous_Goal_MTD OUTPUT;

DECLARE @Current_Churn_MTD int, @Current_Goal_MTD int;
EXEC [dbo].[sp_KTI_Churn_Current_Year] @Current_Churn_MTD = @Current_Churn_MTD OUTPUT, @Current_Goal_MTD = @Current_Goal_MTD OUTPUT;

DECLARE @Left_Than_Previous_Churn_MTD int, @Less_Than_Previous_Goal_MTD int;
EXEC [dbo].[sp_KTI_Churn_Less_Than_Previous_Year] @Left_Than_Previous_Churn_MTD = @Left_Than_Previous_Churn_MTD OUTPUT, @Less_Than_Previous_Goal_MTD = @Less_Than_Previous_Goal_MTD OUTPUT;

SELECT @Previous_Churn_MTD + @Previous_Goal_MTD
    + @Current_Churn_MTD + @Current_Goal_MTD int
    + @Left_Than_Previous_Churn_MTD + @Less_Than_Previous_Goal_MTD;

Answer 2

Try something like this:

CREATE TABLE #Churn(
  Current_Churn INT NOT NULL,
  Goal_Churn INT NOT NULL,
  Caption NVARCHAR(100) NOT NULL
)

INSERT INTO #Churn
EXEC Churn_MTD_Current_Year
INSERT INTO #Churn
EXEC Churn_MTD_Previous_Year
INSERT INTO #Churn
EXEC Churn_MTD_Less_Than_Previous_Year

SELECT Caption
      ,SUM(Current_Churn) AS Current_Churn
      ,SUM(Goal_Churn) AS Goal_Churn
FROM #Churn
GROUP BY Caption

DROP TABLE #Churn