-1

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:

enter image description here

0
3

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;
0
-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
4
  • also can we do it as a store procedure and declare 8 variables better?1-Churn_MTD_Current_Year 2-Churn_MTD_Previous_Year 3-Churn_MTD_Less_Than_Previous_Year 4-MTD_Current_year_goal 5-MTD_Previous_Year_goal 6-MTD_Less_Than_Previous_Year_goal 7-Sum total Churn 8-Sum total goal ?????????????
    – Alex
    Jan 23 '16 at 7:15
  • You can use OUTPUT parameters to store results from the stored procedures into variables. The query I wrote gives you the sum for each column: 7 + 77 + 53 = 137; 13 + 72 + 29 = 114.
    – Serge
    Jan 23 '16 at 7:38
  • There you go: sqlfiddle.com/#!6/deea29/1.
    – Serge
    Jan 23 '16 at 9:20
  • when i execute it ,it gives me an error :((Msg 201, Level 16, State 4, Procedure sp_KPI_Churn_total, Line 0 Procedure or function 'sp_KPI_Churn_total' expects parameter '@Current_Churn', which was not supplied.
    – Alex
    Jan 25 '16 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.