Skip to main content

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?

Comments

Popular posts from this blog

Hosting WCF services on IIS or Windows Services?

There came one of those questions from the client whether to use II7 hosting or windows service hosting for WCF services. I tried recollecting a few points and thought of writing it down.
WCF applications can be hosted in 2 main ways- In a Windows service- On IIS 7 and aboveWhen WCF was first released, IIS 6 did not support hosting WCF applications that support Non-HTTP communication like Net.TCP or Net.MSMQ and developers had to rely on hosting these services on Windows Services.With the release of IIS 7, it was possible to deploy these Non-Http based applications also on IIS 7. Following are the benefits of using IIS 7 to host WCF applications
Less development effort
Hosting on Windows service, mandates the creating of a Windows service installer project on windows service and writing code to instantiate the service, whereas the service could just be hosted on IIS by creating an application on IIS, no further development is needed, just the service implementation is n…

Task based Asynchronous pattern, Async & Await and .NET 4.5

One of the key features in .Net 4.5 is to write asynchronous programs much easier. So if I was to write asynchronous programs in .Net 2.0/3.5, I would either follow the event based model or the callback based model. For an example, a synchronous method that does intensive work (say the DoWork()) can be made asynchronous by using the following patterns
1) Implementing the IAsyncResult pattern. in this implementation, 2 methods are exposed for the DoWork() synchronous method, the BeginDoWork() and the EndDoWork() method. The user will call the BeingDoWork() passing in the required parameters and a callback of the delegate type AsyncCallback(IAsyncResult). The BeginDoWork() will spawn a new thread a return control back to the user. Once work is completed in the spawned method, as a last step, it will call the inform the AsyncResult implementation, which in turns will call the EndDoWork() (which is the callback that was passed in to the BeginDoWork()).
2) Implementing the event pattern. Her…

MEF (Managed Extensibility Framework), .NET 4, Dependency Injection and Plug-in Development

Almost after .Net 4 was released I remember reading about MEF (Managed Extensibility Framework), this was a framework for developers to compose their application with required dependencies. At first this looks like the Unity Container used for dependency injection, but MEF is much more than a dependency container but there is nothing stopping you from using MEF as a dependency injector.I remember around 5 years back when I was in a project that created a framework that allows developers to plug-in there modules as WinForm screens. The developer would create a set of screens with the intended functionalities and the drop this component in the bin folder of the framework and then will move on to do some painful configurations for the framework to pick up the module. Dropping the component into the bin folder and doing a bit of configuration is all that s needed for the framework to pick up display the screens. Typically, the configurations would also contain metadata about the screen.Al…