Common Table Expressions (CTE's) are a handy feature introduced in SQL Server 2005. Making them more handy is the fact that they can be used recursively. This opens up a number of possibilities; one of which being a set based way to build a virtual table out of a comma separated list and filter off of that virtual table. Lets say for example we have a comma separated list of ID's we want to use to filter the results of a query.

DECLARE @Filter varchar(1000)

SET @Filter = '4,8,23,56,72';

You'll notice the semicolon at the end of the SET statement. The next statement in this example will be the CTE and TSQL requires the CTE and the previous statement to be separated by a semicolon. Now for the the first rendition of the CTE.

WITH Filter(FilterId, Position) AS
(
    SELECT 
    CAST(SUBSTRING(@Filter, 1, 
        CASE CHARINDEX(',', @Filter) 
        WHEN 0 THEN LEN(@Filter) 
        ELSE CHARINDEX(',', @Filter) - 1 END
    ) AS int) AS FilterId,
    CHARINDEX(',', @Filter) AS Position
    WHERE @Filter IS NOT NULL AND LEN(@Filter) > 0
)

SELECT * FROM Filter

The CTE, so far, only contains one query which will serve as the "anchor". If you run this it only returns one result, the very first id and the current position in the string:

Next we will add in the recursive query. This query must come immediatly after the anchor and be separated from it by the UNION ALL operator. You can have multiple anchors and multiple recursive queries which can make use of other combination operators, but anchors must be grouped before the recursive queries and the two groups must be separated by the UNION ALL operator.

WITH Filter(FilterId, Position) AS
(
    SELECT 
    CAST(SUBSTRING(@Filter, 1, 
        CASE CHARINDEX(',', @Filter) 
        WHEN 0 THEN LEN(@Filter) 
        ELSE CHARINDEX(',', @Filter) - 1 END
    ) AS int) AS FilterId,
    CHARINDEX(',', @Filter) AS Position
    WHERE @Filter IS NOT NULL AND LEN(@Filter) > 0

    UNION ALL

    SELECT 
    CAST(SUBSTRING(@Filter, 
        Position + 1, 
        CASE CHARINDEX(',', @Filter, Position + 1) 
        WHEN 0 THEN LEN(@Filter) - Position 
        ELSE CHARINDEX(',', @Filter, Position + 1) - Position - 1 END
    ) AS int) AS FilterId,
    CHARINDEX(',', @Filter, Position + 1) AS Position
    FROM Filter WHERE Position > 0
)

SELECT * FROM Filter

The recursive query makes use of the last position to "move" to the next id in the string. When it has hit the end of the string the last position is set to zero which terminates the recursion. The results are as follows:

You'll also notice that the query that selects from the CTE immediately follows it; this is another requirement for CTE's. Now lets select some employees from the AdventureWorks database:

DECLARE @Filter varchar(1000)

SET @Filter = '4,8,23,56,72';

WITH Filter(FilterId, Position) AS
(
    SELECT 
    CAST(SUBSTRING(@Filter, 
        1, 
        CASE CHARINDEX(',', @Filter) 
        WHEN 0 THEN LEN(@Filter) 
        ELSE CHARINDEX(',', @Filter) - 1 END
    ) AS int) AS FilterId,
    CHARINDEX(',', @Filter) AS Position
    WHERE @Filter IS NOT NULL AND LEN(@Filter) > 0

    UNION ALL

    SELECT 
    CAST(SUBSTRING(@Filter, 
        Position + 1, 
        CASE CHARINDEX(',', @Filter, Position + 1) 
        WHEN 0 THEN LEN(@Filter) - Position 
        ELSE CHARINDEX(',', @Filter, Position + 1) - Position - 1 END
    ) AS int) AS FilterId,
    CHARINDEX(',', @Filter, Position + 1) AS Position
    FROM Filter WHERE Position > 0
)

SELECT EmployeeID, LoginID, Title 
FROM HumanResources.Employee
WHERE EmployeeID IN (SELECT FilterId FROM Filter)

And here are the results: