Stored Procedure with dynamic result into temp table

Question

I have a stored procedure in SQL 2014 that makes a dynamic query inside and return two static columns and the rest of columns are dynamic. If I need to put the result of my sp into a temp table, I need to create my temp table specifically with the name of the columns and data types.

How can I just put the result into a temp table without declaring the temp table?

This is how I call it:

SELECT * INTO #TempTable 
FROM OPENROWSET
('SQLNCLI','Server=cimplsql-d1.turtle.local;Trusted_Connection=yes;', 'EXEC [dbo].[AttributeSelect] ''2016-01-01'', ''2016-01-01'', 1')

I can't make my temp table well defined because the result is dynamic, it can return 4, 10 or 20 columns, I don't know.

This is the error now:

The metadata could not be determined because statement 'EXEC SP_EXECUTESQL @Qry;' in procedure 'AttributeEntitiesSelect'  contains dynamic SQL.  Consider using the WITH RESULT SETS clause to explicitly describe the result set.

I used the option WITH RESULT SETS UNDEFINED on my dynamic sql inside the sp, but didn't work

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) — JamieD77, Feb 10 '16 at 19:13

score 1 · Answer 1 · edited May 23 '17 at 12:23

1

You need to use open rowset

SELECT * INTO #TempTable 
FROM OPENROWSET
('SQLNCLI','Server=yourservername;Trusted_Connection=yes;',
     'EXEC OtherDb.DataProd.abc')

SELECT * FROM #TempTable

Rowset process the query results,not query.so dynamic table is possible,further you will need to enable adhoc distributed queries as well

Extract from MSDN:

Is a string constant sent to and executed by the provider. The local instance of SQL Server does not process this query, but processes query results returned by the provider, a pass-through query. Pass-through queries are useful when used on providers that do not make available their tabular data through table names, but only through a command language. Pass-through queries are supported on the remote server, as long as the query provider supports the OLE DB Command object and its mandatory interfaces

References: Exec stored procedure into dynamic temp table

Edited as per error message:
I ran profiler to see what is happening when we call rowset,i could see rowset uses below sp to get metadata of table to insert ..

try executing below sp,if it doesn't work problem is with your sp..

exec [sys].sp_describe_first_result_set N'EXEC tempdb.[dbo].usp_test ''sateesh''',NULL,1

Further I created an sp to just test,i could see no issues..

select * into #temp from openrowset
('SQLNCLI','Server=tejith\sateesh;Trusted_Connection=yes;', 'EXEC tempdb.[dbo].usp_test ''sateesh''')

edited May 23 '17 at 12:23

Community

1
1

answered Feb 10 '16 at 19:12

TheGameiswar

27,855
8
56
94

but I'm on the same db, I still need to use this way? – carlosm Feb 10 '16 at 19:13
Edited ,please see if it helps – TheGameiswar Feb 10 '16 at 19:20
I'm getting this error: Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 158 The metadata could not be determined because every code path results in an error; see previous errors for some of these. Msg 2812, Level 16, State 62, Procedure sp_describe_first_result_set, Line 158 Could not find stored procedure 'pit.AttributeEntitiesSelect'. – carlosm Feb 10 '16 at 19:24
may be you are providing your spname wrong.if I have an sp in a database named test,i go like exec test.dbo.usp_testproc – TheGameiswar Feb 10 '16 at 19:26
1

apparently it doesn't like temp table inside my stored proc, now I have this one: The metadata could not be determined because statement uses a temp table. – carlosm Feb 10 '16 at 19:56
if the temp table columns are well defined,then I don't see any issue with it,please do extend your question marking edited so as not to extend comments – TheGameiswar Feb 10 '16 at 19:59

score 0 · Answer 2 · answered Feb 10 '16 at 21:08

Since your query is run against the same server (should also work against a remote server, though), I think you can tackle it like this:

1) Define a linked server that points to the same instance. Let's call it loopback.

2) Use OPENQUERY

SELECT * INTO #TempTable 
FROM FROM OPENQUERY(loopback, 
    'set fmtonly off exec loopback.yourdb.dbo.yourprocedure 
    WITH RESULT SETS ((@param1 INT, @param2 VARCHAR(20)))');

WITH RESULT SETS is required since SQL2012.

Stored Procedure with dynamic result into temp table

2 Answers2

Linked