Thursday, December 12, 2013

Parametrized OpenQuery

While working on an ETl routine, I need to pass parameter to OpenQuery sql.

I searched and found on most of the place that it can be done only with dynamic sql. I want to avoid dynamic sql.
atlast I found Jeff Moden post on sqlservercentral.com. I am posting it here with the intentions that it may help more peoples and  may come up in search engine results.
Thanks to Jeff Moden.


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 

No comments:

Post a Comment