Get schema of proc's select output

Question

I'd like to put the results of a stored proc into a temp table. It seems that the temp table must be defined beforehand and an INSERT INTO will not work.

Anyone know how to get the schema of the recordset being returned from a select statement?

sp_help only gets info on parameters.

Answer 1

You should be able to insert into a temp table without defining the schema using OPENQUERY:

SELECT * INTO #TempTable
FROM OPENQUERY(ServerName, ‘EXEC DataBaseName.dbo.StoredProcedureName paramvalues1, paramvalues1′)

Where ServerName is the name of your Sql Server instance. See this article for more info

Answer 2

Can you execute the logical content including INSERT INTO in a query window? That should generate a temp table that you can use as a model.

Worst case you build the schema by hand, once, which shouldn't be onerous if you are the one writing the SP.

For the benefit of future documentation, I like to hand-craft DDL in SPs anyway. It helps when debugging to have the schema explicitly at hand.

Answer 3

If you are able, change the stored procedure into a user-defined function.

http://www.scottstonehouse.ca/blog/2007/03/stored-procedures-are-not-parameterized.html