Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » Working with Oracle » Pass a parameter to OpenQuery?
Add to briefcase

Pass a parameter to OpenQuery? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Michael Gerholdt
Posted Thursday, August 30, 2012 1:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 29, 2013 6:32 PM
Points: 43, Visits: 184
Why does not the four-part naming convention work?!?

I need to pass a parameter and am struggling to make this work in OpenQuery:

@STUD_ID is varchar(8) and is the incoming parameter to a stored procedure which places an openquery call, passing along the parameter once again:

select <stuff ...>
from OPENQUERY(Banner_Prod, 'SELECT a.Major
,a.FIRSTNAME AS NameFirst
,a.MIDDLENAME AS NameMiddle
,a.LASTNAME AS NameLast
,a.CLASSCODE AS PersonSubType
FROM fredtrailer.student_Directory_info a
WHERE a.STUD_ID = ''''' + @STUD_ID + '''''')

How to do this? I've played with the number of single quotes. I also put the sql query into a variable and tried to put the @SQLQuery variable in place of the text, but it wants text there, apparently.

Thanks for any help!
Post #1352433
Jo Pattyn
Posted Thursday, August 30, 2012 1:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:57 PM
Points: 1,329, Visits: 4,299
You could resort to dynamic sql, double every single quote.
something like

set @mysql='select .... from openquery(banner_prod,''SELECT a.Major
,a.FIRSTNAME AS NameFirst
,a.MIDDLENAME AS NameMiddle
,a.LASTNAME AS NameLast
,a.CLASSCODE AS PersonSubType
FROM fredtrailer.student_Directory_info a
WHERE a.STUD_ID ='''+@STUD_ID+''')'
execute @mysql

more information on dynamic sql and its dangers
Post #1352474
Michael Gerholdt
Posted Thursday, August 30, 2012 2:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 29, 2013 6:32 PM
Points: 43, Visits: 184
Thanks, Joe, once again.

I just came back to report that I had resolved it in exactly that way. And you beat me to it.
Post #1352488
Jeff Moden
Posted Thursday, August 30, 2012 8:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 32,903, Visits: 26,784
It's been several years since I've had to work with OPENQUERY but I believe the following will work...

select <stuff ...>
from OPENQUERY(Banner_Prod, 'SELECT a.Major
,a.FIRSTNAME AS NameFirst
,a.MIDDLENAME AS NameMiddle
,a.LASTNAME AS NameLast
,a.CLASSCODE AS PersonSubType
FROM fredtrailer.student_Directory_info a'
) oq
WHERE oq.STUD_ID = @STUD_ID


The only thing that I don't remember is whether or not the external criteria is reflected in the OPENQUERY or if the whole OPENQUERY result set is returned before the criteria is applied. The performance test for that is, of course, simple enough. Give it a try.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1352563
Michael Gerholdt
Posted Friday, August 31, 2012 6:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 29, 2013 6:32 PM
Points: 43, Visits: 184
Jeff,

Thanks for this. I must say that I'm a bit surprised that it is really usable. It looks like it would pull the entire table over from Oracle to SQL Server and then select a single record.

But it is just as performant as the other solution presented here (dynamic SQL) as perceived from the SQL side. I think I'll ask the Oracle DBA to watch on the Oracle side as I query each way to see what kind of difference there may be on that end.

I'll post back with results.

Mike
Post #1352752
Michael Gerholdt
Posted Friday, August 31, 2012 6:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 29, 2013 6:32 PM
Points: 43, Visits: 184
Explain plan and resource consumption reports on the Oracle side actually slightly favor the non-dynamic SQL version, although the difference between the two is negligible.

That's not serious benchmarking, just running each example a couple of times.

Good enough, though, for what I was after.
Post #1352768
Jeff Moden
Posted Friday, August 31, 2012 8:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 32,903, Visits: 26,784
Michael Gerholdt (8/31/2012)
Explain plan and resource consumption reports on the Oracle side actually slightly favor the non-dynamic SQL version, although the difference between the two is negligible.

That's not serious benchmarking, just running each example a couple of times.

Good enough, though, for what I was after.


Excellent! Thank you for checking on the Oracle side. I couldn't remember which way it worked and I really appreciate the feedback.

Shifting gears, this is a pretty good indication that an index (if not already present) on the Oracle side would help, as well.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1352827
Rose Bud
Posted Friday, November 02, 2012 9:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:42 AM
Points: 1,072, Visits: 1,026
Stumbled across this Q&A thread using Google. Jeff's solution was ideal for my situation. Thank you!
Post #1380445
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.