MS SQL server set variable to where clause

Question

I am new in MS SQL server and oracle database I have the following query:

 SELECT * from TBL1 where adc in ('21','24');

but I need something like this:

DECLARE @sqlq nvarchar(500)
    Set @sqlq = '21','23';--(how can I write this)
    SELECT* from OPENQUERY([CBSCSDB], 'SELECT * from TBL1 where adc in (@sqlq)');

problem is how can I set value @sqlq and put it in the select statement in where clause.

Answer 1

You can use table variables

DECLARE @sqlq TABLE
(
    adc VARCHAR(100)
)

INSERT INTO @sqlq VALUES (21);
INSERT INTO @sqlq VALUES (23);

SELECT * FROM TBL1 WHERE adc IN (SELECT adc FROM @sqlsq)

Answer 2

Another option is to use dynamic sql, but this can quickly get messy (counting quote marks can be painful):

Declare @where nvarchar(128) = N'''''21'''', ''''23''''';
Declare @sql nvarchar(4000) = N'Select * From OpenQuery([CBSCSDB], 
    ''Select * From tbl1 Where adc In ('+@where+')'')';
Exec sp_executeSQL @sql;

Answer 3

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;

Answer 4

You can query the 4-part name through the linked server.

SELECT * 
FROM CBSCSDB.your-db.your-schema-here.TBL1 
WHERE adc IN (@sqlq);