If you just want to specify a list of string literals in the IN clause of the OPENQUERY's nested query and are struggling to get the syntax right because the same delimiters that are used to delimit your string values are also used to delimit the nested query, then you simply need to escape them inside the query. Escaping in this case is done by doubling the delimiters, like this:
SELECT
*
FROM
OPENQUERY(
[CBSCSDB],
'SELECT * FROM TBL1 WHERE adc IN (''21'',''23'')'
);
The double apostrophes surrounding the items turn into single apostrophes when the entire statement is parsed. Assuming the linked server is configured properly and TBL1
is a valid dataset name on the remote side, that should get you going.
Things become much trickier, though, if the list must necessarily come from a variable (or parameter). The problem is, there is no easy way to parametrise an OPENQUERY's nested query. In particular, none of these methods will work:
Referencing the calling scope's variables, like this:
OPENQUERY(server_name, 'SELECT * FROM SomeTable WHERE SomeCol = @SomeVar')
The references would be invalid.
Building the query in place, like this (assuming @SomeVar
contains the list and the items are already enclosed in string delimiters):
OPENQUERY(server_name, 'SELECT * FROM SomeTable WHERE SomeCol = ' + CAST(@SomeVar AS varchar(10)))
That would be a syntax error.
The previous limitation is similar to that of stored procedures' parameters: you cannot pass an expression as an SP's parameter either. But in that case you can at least store the expression's result in a variable and pass the variable. Here, with OPENQUERY, you cannot have even that luxury. That is, you cannot store the entire query in a string variable and pass the variable as an argument to OPENQUERY:
SET @sql = 'SELECT * FROM SomeTable WHERE SomeCol = ' + CAST(@SomeVar AS varchar(10));
SELECT * OPENQUERY(server_name, @sql);
because that would also cause a syntax error.
In short, the query parameter of OPENQUERY must be a single string literal and there is no getting around that.
The only way to resolve this, therefore, is to dynamically build and execute the entire statement containing OPENQUERY.
That adds one more level of string nesting, which means that the list must now be passed with items surrounded by pairs of apostrophes rather than single apostrophes. You can still pass them with single apostrophes, though, and do the doubling in your script using the REPLACE function.
So, here is an example of a complete script to implement parametrisation in OPENQUERY:
DECLARE @ItemList nvarchar(500) = N'''21'',''23''';
-- actual value stored will be '21','23'
DECLARE @sql nvarchar(max);
SET @sql =
N'SELECT
*
FROM
OPENQUERY(
[CBSCSDB],
''SELECT * FROM TBL1 WHERE adc IN (' -- this is where you double the delimiters
+ REPLACE(@ItemList, '''', '''''') + N')''
);
';
EXEC sp_executesql @sql;
The script may actually be further improved to allow you to pass the list without enclosing the items in apostrophes at all. The enclosing can again be done with the help of REPLACE:
DECLARE @ItemList nvarchar(500) = N'21,23';
DECLARE @sql nvarchar(max);
SET @sql =
N'SELECT
*
FROM
OPENQUERY(
[CBSCSDB],
''SELECT * FROM TBL1 WHERE adc IN (''''' -- this is where you add double delimiters
+ REPLACE(@ItemList, ',', ''''',''''') + N''''')''
);
';
EXEC sp_executesql @sql;
'21','23'
part to come from a variable or are you looking for any ways to specify a list of string literals inside a query that is itself represented as a string literal? – Andriy M Oct 31 '16 at 12:45