Pass a parameter to OpenQuery?

Question

Post reply

Pass a parameter to OpenQuery?

Michael Gerholdt

SSCommitted

Points: 1907
More actions
August 30, 2012 at 1:02 pm

#280670

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!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32066 More actions · Answer 1

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

Michael Gerholdt SSCommitted Points: 1907 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Gerholdt SSCommitted Points: 1907 More actions · Answer 4

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

Michael Gerholdt SSCommitted Points: 1907 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rose Bud Hall of Fame Points: 3147 More actions · Answer 7

Stumbled across this Q&A thread using Google. Jeff's solution was ideal for my situation. Thank you!

thbaig Hall of Fame Points: 3003 More actions · Answer 8

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 ?

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)