SELECT person_id, person_name
FROM MyUsers
WHERE person_id IN (@search_list);
-- 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;
-- Resultsperson_id person_name
-------- ------------------------------
1327 Joe
1342 John F.
1411 Mary
Labels: row number, t-sql programming