sqlsql-serversql-server-2005t-sqlcommon-table-expression

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


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!


Solution

  • 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