3

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.

  • Why do you mention Oracle? The SQL looks like SQL-Server. – Marco Oct 31 '16 at 12:36
  • @Marco: The query inside OPENQUERY is being run against an Oracle database. – Andriy M Oct 31 '16 at 12:37
  • Do you need the '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
  • yes i want like this, but in the open query its only allow string, thats why (@sqlq) not working, i need somehow print value as string on variable – Shimul Chowdhury Nov 1 '16 at 8:33
3

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)
  • This is not a bad answer if the OP was not doing a cross server query with Oracle. In this case, trying to pass a table variable to Oracle across a linked server will definitely cause issues. Simply joining the table variable with the linked server part of the query will cause all kinds of performance issues. Given that he's only doing it with two values, that might not matter but if this is going to allow for future growth where there may be thousands of values, it will really become a problem. Still +1 for offering one potential solution. – Shooter McGavin Nov 1 '16 at 21:58
3

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;
1

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;
0

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

SELECT * 
FROM CBSCSDB.your-db.your-schema-here.TBL1 
WHERE adc IN (@sqlq);
  • declare @sqlq nvarchar (100) SET @sqlq='''12'',''34''' SELECT* from OPENQUERY([CBSCSDB],'SELECT * from adc where DN_NUM in ('+@sqlq+'))'); giving error : incorrect systex near '+'.Expecting ')'. – Shimul Chowdhury Nov 1 '16 at 8:36
  • I edited my comment for clarity. Please try my statement. You need to provide the DB name and schema name on your linked server CBSCSDB – Arthur D Nov 1 '16 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.