SP results as derived table?
-
Is it possible to retrieve the results of a stored proc as a derived table inside the FROM clause?
Here's the jist of what I want to do:
Select colA, B, C from (execute my_sp @param = 'Foo') as t
If so, what's the right syntax?
-
Impossible, rewrite another proc that'll return the right columns and use it instead.
Unless you want to join that data to another table. Then you have to make a table function that returns the data... which is totally legal.
-
OK, thanks.
-
HTH.
-
well, there is an ugly way but hey its a way:
select spid,dbname
from Openrowset('SQLOLEDB','Data Source =SERVERNAME;Trusted_connection=yes;Initial Catalog=master',
'exec sp_who')
* Noel -
That's truly perverse.
In the best sense of that word.
😉
-
Let's just call that extremely last resort
. -
this can come very handy when you need to join the outcome of two stored procedures, not that I recommend it but for something that runs not very often is nice
* Noel -
Still beats the crap out of
create #temp1
create #temp2
insert exec1
insert exec2
select join
drop #temp1
drop #temp2
-
Check out this link on SQLServerCentral.com for the sp_who3 procedure:
http://www.sqlservercentral.com/scripts/contributions/1109.asp
... by racosta.
In it s/he creates a temp table and then does this insert:
INSERT INTO #tmp_who2
EXEC sp_who2
So it is possible to insert into a temp table from a stored procedure.
You could then slice and dice that table or join it to another as needed. That's about as close as you could come, I think.
G. Milner
-
Thanks all; I did end up with a solution very much like these last two posts, and it's working nicely.
-
just keep in mind that if the proc already perform insert ... exec in it you won't be able to do it from the outside (nesting)
* Noel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply