use results from stored procedure in a select ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

use results from stored procedure in a select ?

Author

Topic

BitShift
Yak Posting Veteran

98 Posts

Posted - 2006-10-09 : 10:05:34

How can I do something like this:
select * from (exec sp_name 'param1')

or how about this

create table #fred
insert into #fred exec some_procedure '3'
select * from #fred
drop table #fred

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-09 : 10:20:06

if you don't know the results of your sproc then use this:


-- 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

select * into #t from openquery(loopback, 'exec yourSprocName') 
select * from #t
drop table #t

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-09 : 12:14:05

BitShift,

If you know the schema of resulting output and its pretty stable, it's always better to create table first and insert the data as you shown in your option.

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources