Undefined error in SQL Server Code before "IF"

Question

I'm obviously new to this and I need some help. I have this code in SQL Server Management Studio.

The error that I get is

Msg 156, Level 15, State 1, Line 38
Incorrect syntax near the keyword 'IF'

If I execute direct select statement instead of using "If" the query is successfully executed.

Please point out what I am missing and where is my mistake.

Thanks a lot.

declare @dateFrom [date] = cast(dateadd(day, -7, getdate()) as [date]);
declare @dateTo [date] = cast(dateadd(day, 28, getdate()) as [date]);
declare @prTypeCode [varchar](10) = null;
declare @ExcludeA [bit] = 0 ;
declare @ExcludeB [bit] = 0;

;with distribution as 
(
    select * 
    from [schema1].[Table1]
),
product as 
(
    select * 
    from [schema1].[Product]
),
fint as 
(
    select 
        *,
        (case when CHARINDEX('0%', FIN_ CODE) > 0 then 1 else 0 end) as ZFlag 
    from [schema1].[FinTab]
),
deal as 
(
    select 
        *, 
        case when (AG_ID = 5 or DE_ID = 6) then 1 
else 0 end as Ex_flag 
    from 
        [P_ BASE1].[ schema2].[table3]
),
dealersn as  
(
    select distinct * 
    from [P_ BASE1].[ schema2].[table4]
),
regionN as 
(
    select distinct * 
    from [P_ BASE1].[ schema2].[table5]
),
final as 
(
    select  
        d.PRODUCT_CODE, p.PR_NAME, p.PR_TYPE_CODE, p.PR_ACTIVE,
        d.FIN_TABLE_CODE, ft.FT_NAME, ft.FT_ACTIVE, 
        cast(d.SND_TO_DATE as [date]) as SND_TO_DATE,
        count(d.SN_ID) as SHOP_COUNT,
        min(ft.ZFlag) as ZFlag,
        min(ds.Ex_flag) as ExFlag, dn.SND_FULLNAME,rn.REG_NAME
    from 
        distribution d
    left outer join 
        product p on p.PRODUCT_CODE = d.PRODUCT_CODE
    left outer join 
        fint ft on ft. FIN_ CODE = FIN_ CODE
    left outer join 
        deal as ds On d.SN_ID = ds.SHOP_ID
    left outer join 
        dealersn as dn on d.SN_ID = dn.SN_DATA_ID
    left outer join 
        regionN as rn ON ds.REGION_ID = rn.REGION_ID
    where 
        d.SND_TO_DATE between @dateFrom and @dateTo
        and (@prTypeCode is null or p.PR_TYPE_CODE = @prTypeCode)
    group by 
        rn.REG_NAME, ds.REGION_ID, ds.SHOP_ID, d.PRODUCT_CODE, p.PR_NAME, 
        p.PR_TYPE_CODE, p.PR_ACTIVE, d.FIN_TABLE_CODE, ft.FT_NAME, 
        ft.FT_ACTIVE, d.SND_TO_DATE,dn.SND_FULLNAME
    )
IF (@ZeroPercent = 0 AND @LargeChainsOrTushev = 0)
BEGIN
    SELECT DISTINCT *
    FROM final
    WHERE ZFlag = 0 AND ExFlag = 0
END 
ELSE
    IF @ZeroPercent = 1 AND @LargeChainsOrTushev = 0
    BEGIN
        SELECT DISTINCT *
        FROM final
        WHERE ZFlag = 1 AND ExFlag = 0
    END 
    ELSE IF @ZeroPercent = 0 AND @LargeChainsOrTushev = 1
    BEGIN
        SELECT DISTINCT *
        FROM final
        WHERE ZFlag = 0 AND ExFlag = 1
    END 
    ELSE
    BEGIN
        SELECT DISTINCT *
        FROM final
    END 

Answer 1

You have a with statement that "defines" a bunch of tables, the last of which is called final. This is called a CTE (Common Table Expression). You can use CTEs for many reasons, but the important thing is, you have to use the CTE in a query. Instead you define the CTE and then...do nothing.

If you put a line like this:

select * from final

just after the CTE (before the IF), it will run.