Sunday, May 2, 2010

Passing in parameters into OPENQUERY

I was struggling for some time now, trying to pass in parameters into OPENQUERY, OPENQUERY does not support passing in parameters, all it does it takes a string value as the 2nd parameter like this,

SELECT * FROM OPENQUERY(LINKSERVER_NAME, 'SELECT * FROM COUNTRY WHERE COUNTRYID = 10')

What's worse, it does not support passing in a varchar variable as the 2nd parameter.

So, if you want to pass in parameters, then one of your option is creating a dynamic query and executing it like this

declare @var int = 10
declare @query varchar(max) =
'select * from openquery(Test_link,' + ''''
+ 'SELECT * FROM dbo.TEMP where ID > ' + CAST(@var AS VARCHAR(MAX))
+ '''' + ')'


execute(@query)


IF you want to use the result returned by OPENQUERY, like for an example for joining to another source table, you would have to create a table variable, populate your result and start joining, and illustration would be like this (hypothetical example);

declare @var int = 10
declare @query varchar(max) =
'select * from openquery(Test_link,' + ''''
+ 'SELECT * FROM dbo.TEMP where ID > ' + CAST(@var AS VARCHAR(MAX))
+ '''' + ')'

declare @table Table( id int, name varchar(max))

insert into @table
execute(@query)

select * from @table r
inner join temp t
on r.id = t.ID
where r.name = 'M';

But I would like a better way to do this, for 3 reasons
1) I am executing dynamic queries, so my performance is not what I want.
2) Results returned by OPENQUERY can be joined to another source, but with the method show above, I have to opt to a table variable.
3) Inefficient string concatenations.

Any better solution to this?

No comments:

Post a Comment