Creating a usable comma-seperated INT list variable in SQL

Question

Trying to create a query that declares a variable that stores a comma-separated int list then re-uses that list in a WHERE statement later on. The parameters will have to be passed in the format IN (int,int,int) etc... and will always change dependent on value of @TG. Below is what I have so far:

DECLARE @TG int = 14168

DECLARE @TG_ITEMS varchar =  (SELECT  
                             STUFF((SELECT DISTINCT  
                                ',' + CONVERT(varchar(10),  i.item_key, 120)
                                    FROM store__tracking_group sitg
                                    JOIN store_tracking_group_detail sit 
                                    JOIN item i 
                                    WHERE sitg.number IN (@TG)  
                                    FOR XML PATH ('')), 1, 1, ''))




--- MAIN QUERY HERE

SELECT ''
FROM ''
WHERE 'xx' IN (@TG_ITEMS)

Assuming SQL server, guessing by the syntax - this won't work without dynamic SQL, or a function which splits that string into a table somehow. Why can't you create a temporary table/table variable which holds your list of IDs, and then join to that in your other queries? If you're on version 2008+, the preferable way of doing this is a table valued parameter. — Bridge
– Bridge, Commented Feb 4, 2016 at 17:06
Just create a temp table with the ids you want. Then join on the table. No need to convert to a string. — Carra
– Carra, Commented Feb 4, 2016 at 17:10
The IN operator expects a list of values separated by commas, while you try to give it a single value containing comma-delimited data. That's the difference between IN(1,2,3,4) and IN('1,2,3,4') - the first one will work. — Zohar Peled
– Zohar Peled, Commented Feb 4, 2016 at 17:23

Gordon Linoff · Accepted Answer · 2016-02-05 03:03:03Z

2

Wrong approach. SQL databases have a great data structure for storing lists. It is called a table, not a character string.

One way to do what you want in spirit is a table variable:

DECLARE @TG int = 14168;

DECLARE @TG_ITEMS TABLE (item_key int);

INSERT INTO @TG_ITEMS(item_key)
    SELECT DISTINCT i.item_key
    FROM store__tracking_group sitg JOIN
         store_tracking_group_detail sit 
         ON ??? JOIN
         item i 
         ON ???
    WHERE sitg.number = @TG; 

SELECT ''
FROM ''
WHERE 'xx' IN (SELECT item_key FROM @TG_ITEMS);

This has many benefits, such as readability and performance.

edited Feb 5, 2016 at 3:03

answered Feb 4, 2016 at 17:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Creating a usable comma-seperated INT list variable in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related