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.

7 replies
  1. Phil
    Phil says:

    This probably is not as efficient but

    select * from myUsers
    where
    @delimeter + @searchlist + @delimeter LIKE ('%' + @delimeter + CAST(person_id AS VarChar) + @delimeter + '%')

    Reply
  2. Mohammad
    Mohammad says:

    Hi Plamen,
    In this case is much easier to use dynamic SQL instead of splitting/parsing approach.
    Dynamic SQL like this:

    DECLARE @search_list VARCHAR(100),
    @SQLcommand VARCHAR(MAX);

    SELECT @search_list = '1327,1342,1411',
    @SQLcommand = 'SELECT person_id, person_name
    FROM MyUsers
    WHERE person_id IN ('+ @search_list +');';
    EXEC (@SQLcommand);

    Also I have created a very simple set-based technique for splitting a string with number table, see:

    CREATE FUNCTION dbo.splitter (@S VARCHAR(MAX), @D CHAR(1)) RETURNS TABLE AS
    RETURN (SELECT CASE WHEN CHARINDEX(@D, @S + @D, nbr) – nbr = 0 THEN ''
    ELSE SUBSTRING(@S, nbr, CHARINDEX(@D, @S + @D, nbr) – nbr)
    END AS Word, nbr
    FROM Nums
    WHERE nbr <= LEN(@S)
    AND SUBSTRING(@D + @S, nbr, 1) =@D
    );

    Reply
  3. Mohammad
    Mohammad says:

    Okay,
    I have read some of this article; also the pattern matching approach is simple and short by seems it not efficient. I am right?

    DECLARE @search_list VARCHAR(100) ='1327,1342,1411'

    SELECT person_id, person_name
    FROM MyUsers
    WHERE ',' + @search_list + ',' LIKE '%,' + CAST(person_id AS VARCHAR)+ ',%'
    ORDER BY person_id;

    Reply
  4. Anonymous
    Anonymous says:

    I found the easiest way to accomplish this was to use a temp table.

    IF OBJECT_ID(N'tempdb..#TempTable', N'U') IS NOT NULL
    DROP TABLE [#TempTable]

    Create Table #TempTable (TempField1 varchar(1) not null)

    insert into #TempTable VALUES ('C');
    insert into #TempTable VALUES ('D');
    insert into #TempTable VALUES ('J');
    insert into #TempTable VALUES ('L');
    insert into #TempTable VALUES ('M');
    insert into #TempTable VALUES ('N');
    insert into #TempTable VALUES ('P');
    insert into #TempTable VALUES ('R');
    insert into #TempTable VALUES ('S');
    insert into #TempTable VALUES ('X');
    insert into #TempTable VALUES ('Y');

    SELECT * FROM TableName WHERE Feild1 IN (SELECT TempField1 FROM #TempTable);

    IF OBJECT_ID(N'tempdb..#TempTable', N'U') IS NOT NULL
    DROP TABLE [#TempTable]

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.