How can I use if statement after a CTE (SQL Server 2005)

Common table expressions are defined within the context of a single statement:

WITH cte_name AS (  <cte definition>)<statement that uses cte>;

So you can do something like:

WITH CTEAS(     SELECT * FROM SOMETABLE)SELECT * FROM CTE;

or

WITH CTEAS(     SELECT * FROM SOMETABLE)UPDATE CTE SET somefield = somevalueWHERE id = somekey;

A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view

The closest you'll get is using a UNION ALL to do a crude switched select:

DECLARE @ROLEID AS INTSELECT @ROLEID = [ROLE ID] FROM TBLROLE;WITH CTEAS(     SELECT * FROM SOMETABLE)SELECT    --somecolumnsFROM    CTE    --other stuff tooWHERE    @ROLEID = 1UNION ALLSELECT    --somecolumnsFROM    CTE    --other stuff tooWHERE    @ROLEID = 2UNION ALLSELECT    --somecolumnsFROM    CTE    --other stuff tooWHERE    @ROLEID = 3...UNION ALLSELECT    --somecolumnsFROM    CTE    --other stuff tooWHERE    @ROLEID = n

A little late but I can't be the only one bumping into this.

A solution could be to create a temporary table like this:

-- If previous run of this query fails, the temp table will be deleted.-- Selecting into creates the temp table which fails if it already existsIF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#dtBalansOpgesteldGefilterd%') BEGIN   DROP TABLE #tempEND;;WITH CTEAS(     SELECT * FROM SOMETABLE)-- Followed by select statement as requiredSELECT *INTO #tempFROM CTEIF @awsome = 1BEGIN    SELECT 'WHATEVERYOUWANT' AS WhateverColumnNameYouWant, *    FROM #tempEND