CODEWITHSUNDEEP
sqlsql-server
mike G
mike G

Reputation: 27

Creating a usable comma-seperated INT list variable in SQL

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)

Upvotes: 0

Views: 398

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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.

Upvotes: 2

Related Questions