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

Question

Last night I was writing a simple T-SQL program something like this

DECLARE @ROLEID AS INT  SELECT @ROLEID = [ROLE ID] FROM TBLROLE  ;WITH CTE AS (      SELECT * FROM SOMETABLE ) IF (@ROLEID  = 1)  BEGIN       //SOMECODE END ELSE IF(@ROLEID  = 2)  BEGIN       //SOMECODE END ELSE BEGIN        //SOMECODE END 

I found after compilation that it is throwing error something like "Incorrect statement near if"

What is wrong?

However, I did that by using some other way. But I wanted to know why it did not work!

Answer 1

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 CTE AS (      SELECT * FROM SOMETABLE ) SELECT * FROM CTE; 

or

WITH CTE AS (      SELECT * FROM SOMETABLE ) UPDATE CTE  SET somefield = somevalue WHERE 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

Answer 2

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

DECLARE @ROLEID AS INT  SELECT @ROLEID = [ROLE ID] FROM TBLROLE  ;WITH CTE AS (      SELECT * FROM SOMETABLE ) SELECT     --somecolumns FROM     CTE     --other stuff too WHERE     @ROLEID = 1 UNION ALL SELECT     --somecolumns FROM     CTE     --other stuff too WHERE     @ROLEID = 2 UNION ALL SELECT     --somecolumns FROM     CTE     --other stuff too WHERE     @ROLEID = 3 ... UNION ALL SELECT     --somecolumns FROM     CTE     --other stuff too WHERE     @ROLEID = n