CTE in SQL Server 2005 at 8:44 PM

CTE (common table expression) is a good feature in SQL Server 2005 to make recursive logic easier. But the implementation of SQL Server 2005 has limitation. Let's look at an example as below to get directory tree:

DECLARE @DirID int
SET @DirID = 11;

WITH cte_subDir (dirID, dirName, parentDirID)
AS
(
SELECT dirID, dirName, parentDirID
FROM DirTable
WHERE dirID = @DirID

UNION ALL

SELECT dirID, dirName, parentDirID
FROM DirTable
INNER JOIN cte_subDir
  ON DirTable.parentDirID = cte_subDir.DirID
)

IF @DirID > 10
DELETE FROM DirTable
WHERE dirID IN (SELECT dirID FROM cte_subDir)

When I ran the script, I got this error:

Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'IF'.

It took me about one hour that I realized CTE With statement cannot be followed by IF statement. It can be followed by SELECT statement though.

To make the above logic, I have to create a temporary table using

SELECT * INTO #tempTable FROM cte_subDir

Then, I can use #tempTable for the remaining logic.