Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> Transact-SQL

Return results of stored procedure

I need to return the results of a stored procedure into a temporary table. Something like this:

Select * into #temp from exec (the stored procedure). It appears that I can not do this.

The following will not work for me cause I am not sure how many columns will be returned. I want this to work even if the calling stored procedure is changed (i.e add or take away columns)

insert into (...) exec (the stored procedure.

Does anyone have any ideas how I could do this.

[508 byte] By [Marie05] at [2007-12-25]
«« the progress bar didn't changed when the progresschanged occur of the BackgroundWorker
»» Requires Code to connect SSIS using ODBC source
# 1

Use OPENROWSET

SELECT * into #Temp
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec DbName.dbo.ProcName')
AS tbl

SELECT * FROM #Temp


Here is an example you can run to see how this works
SELECT * into #TempSpWho
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
AS tbl

SELECT * FROM #TempSpWho


Denis The SQL Menace
http://sqlservercode.blogspot.com/

SQL_Menace at 2007-10-8 > top of Msdn Tech,SQL Server,Transact-SQL...
SQL Server
Transact-SQL

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified