Unable to load stored procedure result set into temporary table in SQL Server

Question

I am trying to load stored procedure result set into a temp table, but data is not loading into the temp table. The stored procedure results are not stored into the temp table. It is showing "0 rows affected".

Example #1:

EXEC dbo.emp

(10 row(s) affected)

Example #@:

insert into #tempemp
    EXEC dbo.emp

(0 row(s) affected)

10 rows are not loaded into the temp table.

I tried like this:

insert into #tempemp
   EXEC sp_executesql @tsql = N'EXEC(''EXEC dbo.emp '') 
         with RESULT SETS
(
    (
             EMPID varchar(100)
            ,EMPName VARCHAR(100)
            ,EMPCode VARCHAR(7)
            ,EMPNumber VARCHAR(20)
            ,[STATE] VARCHAR(50)
            ,City VARCHAR(50)
            ,Zip VARCHAR(20)
            ,CustomerTypeName VARCHAR(100)
            ,StatusCD VARCHAR(50)
            ,FilterCode VARCHAR(100) 
    )
 )'

but I only get an error :

Msg 11536, Level 16, State 1, Line 1
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

but stored procedure is returning only one result set.

Can anyone help with this?

Thanks.

I assume you are creating the temp table before running the statement? You haven't included that code. — Jacob H, May 18 '17 at 13:44
You find the workaround [here](http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table/1228165#1228165). It does not work like that, you cannot immediately insert into a table the result from a stored procedure. — Rigerta, May 18 '17 at 13:44
Possible duplicate of [Insert results of a stored procedure into a temporary table](http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) — Sean Lange, May 18 '17 at 13:46
Your stored procedure returns no resultset. Therefore, what you are trying to do is impossible. For this path to be successful, you must change the logic of the stored procedure. — SMor, May 18 '17 at 14:02

score 0 · Answer 1 · answered May 18 '17 at 14:06

0

If testing is to take place, it should be on the same object. None of us have your SP, but I know I have this, and it works just fine on my end:

CREATE PROCEDURE DUCKLING AS BEGIN SELECT 1 END

CREATE TABLE #TEMP (ID INT)

INSERT INTO #TEMP EXEC DUCKLING

SELECT * FROM #TEMP

So maybe you can try that, and/or provide us your SP's code. Whether or not an alternative SP (like DUCKLING) works would help us narrow down where the issue happens.

answered May 18 '17 at 14:06

KtX2SkD

752
4
12

generally your scenario will work.I am using same scenario in other stored procedures.but problem with stored procedure is I have used many temp tables and stored procedures inside sp dbo.emp for that reason data not loading into temp table.I thought meta data issue for temp tables.so I changed temp table into table variable still not loading data. Thanks for the Response. – Rajashekhar May 19 '17 at 04:14

score 0 · Answer 2 · answered May 19 '17 at 14:23

I can't simply comment yet as I am a noob.

It looks to me as if you are expecting output from exec dbo.emp. I see (10) rows but I would expect results like you would receive in a select statement.

You likely just need to put a select statement at the end of dbo.emp to dump everything out and then your Insert should work as intended.

Unable to load stored procedure result set into temporary table in SQL Server

2 Answers2