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

user_111663 0
Viewed 55542

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!

common-table-expression sql-server-2005 tsql sql-server sql
4 Answers

Try putting the CTE in the IF. It worked for me.

IF @awsome = 1
BEGIN
;WITH CTE
AS
( 
    SELECT * FROM SOMETABLE
)
    SELECT 'WHATEVERYOUWANT' FROM CTE
END
ELSE IF @awesome = 2
BEGIN
;WITH CTE2
AS
( 
    SELECT * FROM SOMETABLE
)
    SELECT 'WHATEVERYOUWANT' FROM CTE2
END
user_13017340
Related