CODEWITHSUNDEEP
sqlstringselectwhere-clause
Alsjka
Alsjka

Reputation: 55

String variable used in the "IN' statement

I am trying to use a string from a qry that i have built using single quotes and commas. but when i reference the string varbiable it seems to fail. Can some review my script and tell me hat I am missing or hav done wrong please.

Below is my script I am currently trying:

DECLARE @myList TABLE (Id nvarchar(max));

INSERT INTO @myList(Id) VALUES ('40098071,40098081');

select '''' + REPLACE(id,  ',', ''',''') + ''''  from @mylist
select destid from [servername].database.[dbo].table where table in (select '''' + REPLACE(id,  ',', ''',''') + ''''  from @mylist)

I would expect to return data.

Upvotes: 0

Views: 33

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521209

Your current syntax is off, and you could try something like this:

SELECT destid
FROM [servername].database.[dbo].table t1
WHERE EXISTS (SELECT 1 FROM @myList t2
              WHERE ',' + t2.Id + ',' LIKE '%,' + t1.destid + ',%');

Demo

However, it is generally bad practice to store CSV in your SQL tables, because it is unnormalized data. It would be much better to have ID on a separate row, e.g. use the following table:

@myList
40098071
40098081

Then, you could simply use this query:

SELECT destid
FROM [servername].database.[dbo].table t1
WHERE EXISTS (SELECT 1 FROM @myList t2 WHERE t2.Id = t1.destid);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You have a value that is a string with a comma in it. What you really want is:

DECLARE @myList TABLE (Id int);

INSERT INTO @myList (Id)
    VALUES (40098071), (40098081);

SELECT destid 
FROM [servername].database.[dbo].table 
WHERE table IN (SELECT id FROM @mylist);

Queries are not strings, unless you are planning on using dynamic SQL. You are putting the ids into a table, so you might as well put each id in a separate row and make things easy on yourself.

Upvotes: 1

Related Questions