Select columns from (EXEC procedure_name) - Is this possible?

Well.I see many users asking this question in forums. "I have a procedure that returns single resultset. How do I filter the result returned from the procedure? There are, at least, two ways to acheive this

Let us create this procedure

create procedure get_orders

as

select* from northwind..orders 

1 Create a temporary table whose structure is identical to the result of the procedure and query on      this   table

Create table #orders(Orderid int,..................)

Insert into #orders EXEC get_orders

Select* from #orders where orderdate>='19960101' and orderdate<'19970101'

2 Use OPENROWSET 
Select 
           *
from 
          OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes;
          Integrated Security=SSPI','Execute yourdb..get_orders')

Now you can easily filter the resultset

Select 

           employeeid,orderid,orderdate 

from 

           OPENROWSET('SQLOLEDB','Data Source=test;Trusted_Connection=yes;

           Integrated Security=SSPI','Execute yourdb..get_orders')

where

           orderdate>='19960101' and orderdate<'19970101'

I prefer using method 2 as you dont need to create temp table and 
also you dont need to worry about the structure of the procedure
Published 26 November 2007 11:04 by Madhivanan
Filed under: , ,

Comments

# re: Select columns from (EXEC procedure_name) - Is this possible?

26 November 2007 15:52 by Eugene Khazin

Would the same be possible, if the procedure returned more then one result set?

# re: Select columns from (EXEC procedure_name) - Is this possible?

27 November 2007 06:29 by Madhivanan

Method 1 would fail

Method 2 would just return first resultset