Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

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

Nov 24 2007 8:26AM by 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=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

Tags: sql_server, procedure, openrowset,


Madhivanan
3 · 40% · 12862
4
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

2  Comments  

  • Note that in SQL Server 2005 and up, the second way requires altering the server's surface area configuration to allow ad-hoc distributed queries. There may also be issues with permissions and permission chaining. And there are always the problems inherent in switching from direct script to dynamic SQL...

    commented on Jun 15 2011 11:46AM
    ErikEckhardt
    65 · 3% · 898
  • I prefer method #2 too! Thanks for writing this up, Madhivanan!

    commented on Jan 11 2014 7:43AM
    Nakul Vachhrajani
    4 · 36% · 11543

Your Comment


Sign Up or Login to post a comment.

"Select columns from (EXEC procedure_name) - Is this possible?" rated 5 out of 5 by 4 readers
Select columns from (EXEC procedure_name) - Is this possible? , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]