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:
