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: 
[Common table expressions](http://msdn.microsoft.com/en-us/library/ms175972.aspx) 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
> 
> 
>
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 exists
IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#dtBalansOpgesteldGefilterd%') BEGIN
   DROP TABLE #temp
END;

;WITH CTE
AS
( 
    SELECT * FROM SOMETABLE
)

-- Followed by select statement as required
SELECT *
INTO #temp
FROM CTE

IF @awsome = 1
BEGIN
    SELECT 'WHATEVERYOUWANT' AS WhateverColumnNameYouWant, *
    FROM #temp
END

```