Subscribe: Madhivanan : openrowset
http://sqlblogcasts.com/blogs/madhivanan/rss.aspx?Tags=openrowset&AndTags=1
Preview: Madhivanan : openrowset

Madhivanan : openrowset



Tags: openrowset



 



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

Mon, 26 Nov 2007 11:04:00 GMT

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 procedurecreate procedure get_orders asselect* 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 resultsetSelect  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 [...]