Get proc selection output schema

Question

I would like to put the results of a stored procedure in a temp table. It seems that the temporary table should be predefined and the INSERT INTO will not work.

Does anyone know how to get the record set schema returned from the select statement?

sp_help only gets parameter information.

Answer 1

You should be able to insert into a temporary table without specifying a 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 information.

Answer 2

, INSERT INTO, ? , .

, , , , SP.

, DDL SP. , .

Answer 3

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

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