How do I execute an If statement inside a CTE

Question

I need to create a cte based on the value of an SSRS report parameter. The If statement in the attached code executes just fine outside the cte, but errors inside the cte.

I and one of my coworkers have spent several hours unsuccessfully trying to solve this. I have tried using a CASE statement. I have tried placing the WITH cteIngnoreCommID AS inside both the IF and ELSE, but nothing works. This cte will be joined in subsequent code that creates another cte. Hopefully someone can tell me what I need to do.

--Declare and set Report parameter for testing purposes only
Declare @IgnoreYear as varchar(4)='2010'
;
WITH cteIgnoreCommID as (
    IF @IgnoreYear=''
        -- create cte with one record have blank values
        Select '' as IgnoreComm_ID
            ,'' as IgnoreYear
            ,0 as IngnoreBalFwd
    ELSE 
        -- create cte with records obtained from multiple tables.
        -- for proof of concept I am just creating one record.
        Select '30710000' as IgnoreComm_ID
            ,@IgnoreYear as IgnoreYear
            ,5000 as IngnoreBalFwd
                        )
--The above code run just fine if you comment out the cte lines.

I get the correct results if run the IF/ELSE outside the cte. When run as written with the cte I get the following 2 errors:

Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'IF'. Msg 102, Level 15, State 1, Line 13 Incorrect syntax near ')'.

Answer 1

The easiest way would be to use CASE statements inside the CTE. You could modify your CTE to look like this and it should do what you want:

Declare @IgnoreYear as varchar(4)='2010'
;
WITH cteIgnoreCommID as (
        Select CASE WHEN @IgnoreYear='' THEN '' 
                ELSE '30710000' 
                END AS IgnoreComm_ID
            ,CASE WHEN @IgnoreYear='' THEN '' 
                ELSE @IgnoreYear
                END as IgnoreYear
            ,CASE WHEN @IgnoreYear='' THEN 0 
                ELSE 5000
                END as IngnoreBalFwd
     )