Passing a Variable to an IN List

Every once in a while there is a need to do something like this:

SELECT person_id, person_name
FROM MyUsers
WHERE person_id IN (@search_list);

And @search_list contains some form of a delimited list. However, this is not a supported syntax and will fail. Here is one solution to this problem:

-- Create the test table
CREATE TABLE MyUsers (
 person_id INT PRIMARY KEY,
 person_name VARCHAR(35));
 
-- Insert sample data
INSERT INTO MyUsers VALUES (1327, 'Joe');
INSERT INTO MyUsers VALUES (1342, 'John F.');
INSERT INTO MyUsers VALUES (1411, 'Mary');
INSERT INTO MyUsers VALUES (1345, 'Nancy');
INSERT INTO MyUsers VALUES (1366, 'Greg');
INSERT INTO MyUsers VALUES (1367, 'Jeff');
INSERT INTO MyUsers VALUES (1368, 'Chris');
INSERT INTO MyUsers VALUES (1369, 'John M.');
INSERT INTO MyUsers VALUES (1370, 'Peggy');
INSERT INTO MyUsers VALUES (1371, 'Samuel');
INSERT INTO MyUsers VALUES (1372, 'Tony');
INSERT INTO MyUsers VALUES (1373, 'Lisa');
INSERT INTO MyUsers VALUES (1374, 'Tom');
INSERT INTO MyUsers VALUES (1375, 'Dave');
INSERT INTO MyUsers VALUES (1376, 'Peter');
INSERT INTO MyUsers VALUES (1377, 'Jason');
INSERT INTO MyUsers VALUES (1378, 'Justin');
INSERT INTO MyUsers VALUES (1379, 'Oscar');
 
DECLARE @search_list VARCHAR(100);
 
DECLARE @delimiter CHAR(1);
 
SELECT @search_list = '1327,1342,1411',
       @delimiter = ',';
 
-- Get the users based on the delimited variable list
SELECT person_id, person_name 
FROM MyUsers
WHERE person_id IN 
     (SELECT SUBSTRING(string, 2, CHARINDEX(@delimiter, string, 2) - 2)
      FROM (SELECT SUBSTRING(list, n, LEN(list))
            FROM (SELECT @delimiter + @search_list + @delimiter) AS L(list),
                 (SELECT ROW_NUMBER() OVER (ORDER BY person_id)
                  FROM MyUsers) AS Nums(n)
            WHERE n <= LEN(list)) AS D(string) 
      WHERE LEN(string) > 1
        AND SUBSTRING(string, 1, 1) = @delimiter)
ORDER BY person_id;

-- Results

person_id person_name
-------- ------------------------------
1327     Joe
1342     John F.
1411     Mary

Notes:

– This algorithm is based on walking the delimited string (could be in a table column or a variable) and parsing it into a table format that can feed the IN list.

– The use of ROW_NUMBER assumes you are using SQL Server 2005. However, you can accomplish the same if you have SQL Server 2000. What this subquery does (SELECT ROW_NUMBER() OVER (ORDER BY person_id) FROM MyUsers) is simply generating a table with numbers. In SQL Server 2000 you can create a temp table and generate sequential numbers. The only requirement is that you have more numbers than the length of the string to parse (in this case the length of ‘1327,1342,1411’). These numbers are used as an index in the walk process.