When querying an external data source through a linked server, you have two choices: you can write a typical T-SQL query, specifying the external tables with their four-part name in the FROM clause, or use the OPENQUERY function. The first option has advantages, as long as the external provider supports enough SQL so that SQL Server can take your query and translate it into a query that the external data source can use. On the other hand, if th external provider doesn't support enough SQL for SQL Server to optimize the query, or if the external provider is just too plain buggy, you will have to resort to passing through your own provider-specific queries with OPENQUERY. This gives you a great deal of control over how the external data source is accessed, but comes with a frustrating and puzzling limitation: OPENQUERY requires that the query be a string constant. That means no string variables you built with conditional logic, no expressions that result in a string, and especially no support for parameter substitution!

Fortunately, there is an option besides pulling the entire external table over and then processing it on SQL Server. It's an extremely powerful built-in procedure called sp_executesql. This stored procedure accepts a string containing SQL statements and attempts to execute it. However, with this great power comes great responsibility! Since you are constructing a string of ad-hoc SQL, you must be extremely careful to validate all external input lest you fall victim to a SQL Injection attack. Let's look at a simple example of how to use sp_executesql to create a parameterized OPENQUERY against a linked server named NAVISION.

CREATE PROCEDURE OpenQuery_Customer_ByDate( @date datetime )
AS
set nocount on

declare @stmt nvarchar(max)
set @stmt = 'select * from OPENQUERY( NAVISION, ''select * from Customer where "Last Date Modified" >= ' + convert( char(10), @date, 120 ) + ''' )'

exec sp_executesql @stmt

This procedure illustrates the basic idea of the technique: build a string containing the OPENQUERY query, dynamically building the pass-through query within this string. After the string has been built and passed to sp_executesql, the pass-through query appears as if it were a string constant to OPENQUERY. The same technique could be used to dynamically create a view or function with a predictable name that other SQL could use in a SELECT statement. With sp_executesql, you are limited only by what you can write to a string, and that could be anything!