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?
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?
Comments
Post a Comment