SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pass a parameter to OpenQuery?


Pass a parameter to OpenQuery?

Author
Message
Michael Gerholdt
Michael Gerholdt
SSC-Addicted
SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)

Group: General Forum Members
Points: 456 Visits: 240
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!
Jo Pattyn
Jo Pattyn
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7248 Visits: 10021
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
Michael Gerholdt
SSC-Addicted
SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)

Group: General Forum Members
Points: 456 Visits: 240
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
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216998 Visits: 41991
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Michael Gerholdt
Michael Gerholdt
SSC-Addicted
SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)

Group: General Forum Members
Points: 456 Visits: 240
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
Michael Gerholdt
SSC-Addicted
SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)SSC-Addicted (456 reputation)

Group: General Forum Members
Points: 456 Visits: 240
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
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216998 Visits: 41991
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1791 Visits: 1062
Stumbled across this Q&A thread using Google. Jeff's solution was ideal for my situation. Thank you!
thbaig
thbaig
SSChasing Mays
SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)

Group: General Forum Members
Points: 637 Visits: 496
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 Smile

Can we pass Linked Server Name as variable without dynamic sql ?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216998 Visits: 41991
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search