Tuesday, August 25, 2009

Treating The Results of a Stored Procedure as a Table Expression

I stumbled upon a StackOverflow thread this week, where the poster was asking how they could fetch the results of a stored procedure into a temporary table without knowing the resultset’s composition (column layout) in advance. If your first question is “why?”, good for you. If you’re trying to capture the resultset returned by a stored procedure without knowing it’s composition, how in the world can you possibly hope to use said result set?

Whatever the case, the OP clearly wanted to do so. They had gotten as far as concluding that the SELECT…INTO…FROM syntax (which creates the object specified after the INTO keyword) would do the heavy lifting for them, if only they could treat the stored procedure as a table expression (if you have no idea what I’m talking about, check out the examples below). The normal use of the SELECT INTO syntax looks something like this:

SELECT *  --Don't use *.  You didn't see this here.
INTO #MyTempTable --Drop the hash to create a permanent table.
FROM MyRealTable

You can’t substitute a sproc name for your table name, and expect it to work. What you can do is use OPENROWSET or OPENQUERY. Have a look here:

* FROM sys.databases --Or whatever.


INTO #MyTempTable
'EXEC MyProc')

SELECT * FROM #MyTempTable

Neat, eh? Keep in mind that you need to have “Ad Hoc Distributed Queries” enabled:

sp_configure 'Show Advanced Options', 1

sp_configure 'Ad Hoc Distributed Queries', 1


Here’s the original thread – there are a number of very good contributions on the thread.

Thanks to SQL MVP Rob Farley for putting a name to what the OP was asking for – the title of this article was a direct “yoink” from his comment on my post. I would have called it “How to bring back the results of a stored procedure into a temporary table even though you don’t know what the results look like at design time”. I ran short of breath just writing that one.

See you next time.


Michael Swart said...

I can imagine a scenario where you know that a sproc will return at least columns a, b and c and some other columns that you don't care about.

If you declare a temp table to hold the results, then you have to specify all columns (if using the INSERT .. EXEC syntax). Then this temp table would have to maintained with any change to the resultset of the sproc.

I think the OP hinted at something like this although he never came out and said it.

Brad Schulz said...

It goes without saying, perhaps, but I'll say it anyway for others reading the post...

The name of the procedure should be fully qualified in the OPENROWSET or else it won't be found (unless the procedure was created in the master database):

OPENROWSET(...,'EXEC MyDatabase.dbo.MyProc')

Also, as far as what kind of procedure might return an unknown number of columns... it could be a proc that does dynamic SQL... a dynamic PIVOT perhaps... but you're right, once you've put those results INTO a table, then what?

Rob Farley said...

Actually, it's not about using a temporary table, because you could use the OPENROWSET for the basis of a group by, joins, whatever...


sweet said...

Dear Aaron Alton,

I am recent graduate interested in sqlDBA i m working for small company with 10-15 databases relatively small and i m wondering which database backup/restore plan dba's like you usually use