Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pass a parameter to OpenQuery? Expand / Collapse
Author
Message
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: Saturday, March 22, 2014 10:40 PM
Points: 44, Visits: 194
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
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: Today @ 2:01 PM
Points: 1,394, Visits: 6,591
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
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: Saturday, March 22, 2014 10:40 PM
Points: 44, Visits: 194
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
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 @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1352563
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: Saturday, March 22, 2014 10:40 PM
Points: 44, Visits: 194
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
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: Saturday, March 22, 2014 10:40 PM
Points: 44, Visits: 194
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
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 @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1352827
Posted Friday, November 2, 2012 9:23 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:15 AM
Points: 1,447, Visits: 1,059
Stumbled across this Q&A thread using Google. Jeff's solution was ideal for my situation. Thank you!
Post #1380445
Posted Wednesday, December 11, 2013 6:59 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:55 PM
Points: 78, Visits: 248
Jeff Moden (8/30/2012)
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.


Thank you Jeff. This is great to read. I have searched but every solution comes with dynamic sql and I wanted to avoid it :)

Can we pass Linked Server Name as variable without dynamic sql ?
Post #1521881
Posted Wednesday, December 11, 2013 8:32 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
What I have posted isn't Dynamic SQL... it's a hardcoded query like you might have in any stored procedure but it's necessarily stored in a string simply because that's what OPENQUERY requires. I know that OPENROWSET doesn't allow the use of variables and must be dynamic to make changes but I haven't actually tried using a variable with OPENQUERY so I don't know if "simple" query replacement would work here or not. Of course, "full" dynamic SQL like that used for making changes to an OPENROWSET would work but I absolutely understand the desire to avoid such a thing.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1521933
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse