SP into #table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SP into #table

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-06-14 : 10:40:42

How do I select the results of an SP and then select * from that afterwards?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-14 : 10:57:38

CREATE TABLE #table(col1 int, col2 int, col3 varchar(10)) -- design this to match the procedure's output
INSERT INTO #table EXEC myStoredProcedure

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-14 : 11:02:31

depends what your sproc returns.
exec dbo.sproc_name(@parm1, @parm2) should do the trick if your sproc returns a result set

If you don't have the passion to help people, you have no passion

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-06-14 : 11:45:14

If the no of columns inlolved was more than 904, i wouldn't want to create that table. Can't I just stick the results into a self-creating #table?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-14 : 11:49:51

The SELECT...INTO syntax doesn't allow a stored procedure to be executed. Why not just rewrite the stored procedure to insert the results into a table instead of SELECTing them?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-06-14 : 13:00:26

You actually can do this but the technique is highly funky. By establishing a linked server (to itself) and using OPENQUERY you can create the table by using "set fmtonly on".


-- add 'loopback' linkedserver 
if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go

--just create the empty table with this:
--select * into #t from openquery(loopback, 'set fmtonly on exec sp_who') 
--insert #t exec sp_who

--create and insert with this:
select * into #t from openquery(loopback, 'exec sp_who') 


select * from #t
--drop table #t
go

if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-06-20 : 12:29:06

<<
If the no of columns inlolved was more than 904
>>

Are you generating PIVOT data?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources