CODEWITHSUNDEEP
sql-serverstored-proceduresinsertexec
Василий Пупкин
Василий Пупкин

Reputation: 21

INSERT - EXEC problem. Empty output from stored procedure

I need to SELECT specific data from stored procedure output so I am trying to insert the stored procedure's output into a table. I am very newbie in SQL.

So here is the code:

Declare @result table
(
    row_id int,
    quarry nvarchar(400),
    interval nvarchar(100),
    vskr_plan decimal(30,1),
    vskr_fact decimal(30,1),
    vskr_diff decimal(30,1),
    ruda_plan decimal(30,1),
    ruda_fact decimal(30,1),
    ruda_diff decimal(30,1),
    gpr_plan decimal(30,1),
    gpr_fact decimal(30,1),
    gpr_diff decimal(30,1),
    gm_plan decimal(30,1),
    gm_fact decimal(30,1),
    gm_diff decimal(30,1)
)

INSERT INTO @result
    EXEC [report].[DISP_KPI_QUARRY] @datet = N'2018-10-11'

SELECT * 
FROM @result

I have no errors with this code but it returns an empty table!

However if I execute:

EXEC [report].[DISP_KPI_QUARRY] @datet = N'2018-10-11'

I see all the data.

What is my mistake, guys?

Upvotes: 2

Views: 959

Answers (1)

Zhorov
Zhorov

Reputation: 29993

You may try to include your columns in INSERT statement in the same order as they are returned from your stored procedure. When you insert results from stored procedure, you need to be sure, that the count, datatype and order of the columns, that stored procedure returns match your @result table definition. 

DECLARE @result table
(
    row_id int,
    quarry nvarchar(400),
    interval nvarchar(100),
    vskr_plan decimal(30,1),
    vskr_fact decimal(30,1),
    vskr_diff decimal(30,1),
    ruda_plan decimal(30,1),
    ruda_fact decimal(30,1),
    ruda_diff decimal(30,1),
    gpr_plan decimal(30,1),
    gpr_fact decimal(30,1),
    gpr_diff decimal(30,1),
    gm_plan decimal(30,1),
    gm_fact decimal(30,1),
    gm_diff decimal(30,1)
)

INSERT INTO @result (row_id, quarry, interval, vskr_plan, vskr_fact, vskr_diff, ruda_plan, ruda_fact, ruda_diff, gpr_plan, gpr_fact, gpr_diff, gm_plan, gm_fact, gm_diff)
EXEC [report].[DISP_KPI_QUARRY] @datet = N'2018-10-11'

SELECT *
FROM @result

Upvotes: 1

Related Questions