Passing Multiple Values to Variable in Linked Server Connection String

Question

I have the following query, which pulls data from an Oracle DB into SQL Server 2005:

SELECT *
FROM (
    SELECT * 
    FROM OPENQUERY(LINKEDSERVERNAME, 'SELECT FOO, BAR, FROM TABLE
    WHERE ID IN(' + @IDs + '
    ')) AS TMP
WHERE SOME_ID IN
    (SELECT DISTINCT ID
    FROM LOCALTABLE);

The runtime, however, is very long, as the query from the linked server results in a large number of rows. I am only interested in a small number of these rows, however the criteria limiting my query are held in the destination database.

Via another post on SO, I see I could potentially use a variable in dynamic sql that looks like:

DECLARE @IDs AS NVARCHAR(100);

SET @IDs = (SELECT ID FROM LOCALTABLE)

DECLARE @sql AS NVARCHAR(3000);

SET @sql = 'SELECT * FROM OPENQUERY(LINKEDSERVERNAME, ''SELECT FOO, BAR, FROM TABLE
WHERE ID IN(' + @IDs + '))'

EXEC sp_executesql @sql

However, I obviously cannot assign more than one value to the variable, and so the result set only contains results for the final ID placed in @IDs.

What is the best strategy for accomplishing this task for all distinct IDs in the local table?

You can not assign more than one value to variable because the select query for that @ID variable shows it will have only one value. You already define the variable correctly as string, but don't you think you need to do some more in that query to select all values as comma separated values ? The simple plain select will overwrite previously stored value in variable and you will get only last value as final result in your variable. — Anup Shah
– Anup Shah, Commented Oct 8, 2013 at 14:11
I understand that this strategy is flawed, but it's the best I got. Any pointers welcome. — user1893148
– user1893148, Commented Oct 8, 2013 at 14:23

JamesB · Accepted Answer · 2013-10-08 14:23:33Z

1

Anup Shah has already pointed out what is wrong in his comment. Your SELECT assignment will only ever put one value into your variable. You need a way to convert your table results to a CSV style for the IN statement. Pinal Dave has a good post which shows a well known technique for doing this with XML PATH.

http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/

Worth noting that SELECT @var = @var + var FROM table IS NOT a valid way of doing this, although it may appear to work in some cases.

James

answered Oct 8, 2013 at 14:23

JamesB

3253 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1893148 Over a year ago

Thanks. Can you advise the best way to set the variable to the 'SELECT SUBSTRING' query outlined in the blog post?

JamesB Over a year ago

SET @var = ( SELECT SUBSTRING( (SELECT ',' + s.Name FROM HumanResources.Shift s ORDER BY s.Name FOR XML PATH('')),2,200000) AS CSV )

user1893148 Over a year ago

I like this strategy, however because it makes the variable one string (e.g. 'ID1,ID2,ID3') It doesn't pass correctly in the query string, and trips the error Msg 102, Level 15, State 1.. near ','.

user1893148 Over a year ago

So, the query string is actually much more complex. The error seems to be in the number of escapes (') needed around the variable. It is set up as an IN('+ @VARIABLE +') . But the thing is it works if I pass a single ID as the variable, but not if I pass a comma delimited CSV of many IDs. Does that make any sense?

user1893148 Over a year ago

I ended up having to use a cursor.

Collectives™ on Stack Overflow

Passing Multiple Values to Variable in Linked Server Connection String

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related