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

Published 26 November 07 10:54 AM | Madhivanan 

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

Comments

# SSQA.net - SqlServer-QA.net said on November 26, 2007 6:21 AM:

Well.I see many users asking this question in forums. &quot;I have a procedure that returns single resultset

# Other SQL Server Blogs around the Web said on November 26, 2007 6:41 AM:

Well.I see many users asking this question in forums. &quot;I have a procedure that returns single resultset

Anonymous comments are disabled