I have a split function that you can use to parse the string, then join it to the main table.CREATE FUNCTION [dbo].[strSplit] (@arr AS NVARCHAR(MAX), @sep AS NCHAR(1)) RETURNS TABLE AS RETURN WITH L0 AS (SELECT 1 AS C UNION ALL SELECT 1) --2 rows ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B) --4 rows (2x2) ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B) --16 rows (4x4) ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B) --256 rows (16x16) ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B) --65536 rows (256x256) ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B) --4,294,967,296 rows (65536x65536) ,Numbers AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5) SELECT (n - 1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, N'')) + 1 AS pos, LTRIM(SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n)) AS element FROM Numbers WHERE n <= LEN(@arr) + 1 AND SUBSTRING(@sep + @arr, n, 1) = @sep AND Numbers.n <= 1000 declare @emp table (EmpID int ,EmpName varchar(20))insert into @empselect 1, 'Emp1' union allselect 2, 'Emp2' union allselect 3, 'Emp3' union allselect 4, 'Emp4' union allselect 5, 'Emp5' union allselect 6, 'Emp6'declare @EmpId varchar(10)set @empid='1,2,3,4'select e.EmpID, e.EmpName from @emp e inner join [dbo].[strSplit] (@empid, ',') f on e.EmpID = f.element
For Faster results please follow the posting guidelines herehttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx