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=Server_name;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=Server_name;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

# Undefined table from table function? | keyongtech

18 January 2009 17:00 by Undefined table from table function? | keyongtech

Pingback from  Undefined table from table function? | keyongtech

# add another column to output of a stored proc | keyongtech

22 January 2009 03:06 by add another column to output of a stored proc | keyongtech

Pingback from  add another column to output of a stored proc | keyongtech

# Data types of result of stored procedure | keyongtech

21 April 2009 15:07 by Data types of result of stored procedure | keyongtech

Pingback from  Data types of result of stored procedure | keyongtech

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

04 December 2009 22:30 by mycodetrip

@the author,

1. In your #1 option, there is no need to use Temp Tables. You can easily use the TABLE variable instead. I tried it just now and it works. i.e. your code would look like so.

<code>

Create @Orders TABLE (Orderid int,..................)

Insert into @Orders EXEC get_orders

Select* from @Orders where orderdate>='19960101' and orderdate<'19970101'

</code>

2. Your option #2, though preferred, may not be practical in corporate environments, where OPENROWSET is usually disabled for Security reasons...

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

07 December 2009 08:23 by Madhivanan

mycodetrip,

The table variable as suggested in your point 1 works from version 2005 onwards. For the versions 2000 or prior, you need to use a temporary table

# horoscopes

24 December 2011 01:41 by horoscopes

Pingback from  horoscopes