SQL Server中IF关键字附近语法错误求助（附CTE代码）

Hey there! Let's break down your issue and fix your code step by step.

1. 核心错误原因 #

The main problem here lies in how CTEs (Common Table Expressions) work in SQL Server:

• A CTE defined with ;WITH must be followed immediately by a single SQL statement (like SELECT, INSERT, UPDATE, etc.).
• You're trying to follow the CTE directly with an IF flow-control block, which isn't recognized as a valid standalone statement by the SQL engine. That's exactly why you're getting the Msg 156 syntax error near the IF keyword.

2. 其他潜在问题 #

I also caught two more issues that would cause errors once you fix the CTE problem:

• You’re using variables @ZeroPercent and @LargeChainsOrTushev in your IF conditions, but you never declared them at the top of your script.
• In the join between distribution d and fint ft, your condition ft.FIN_ CODE = FIN_ CODE is a self-join on the fint table (you didn’t specify a table alias for the right-hand side), which is almost certainly not what you intended. It should link to the distribution table's relevant column instead.

3. 修正后的代码 #

The most straightforward fix is to first store the CTE results in a temporary table, then use your IF logic to query that table. Here's the corrected version:

-- 补上缺失的变量声明
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;
-- 新增缺失的变量
declare @ZeroPercent [bit] = 0;
declare @LargeChainsOrTushev [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
 -- 修正连接条件，关联到distribution表的对应字段
 left outer join fint ft on ft.FIN_CODE = d.FIN_TABLE_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
 )
-- 将CTE结果插入临时表
SELECT * INTO #TempFinal FROM final;

-- 现在可以正常使用IF逻辑查询临时表
IF (@ZeroPercent = 0 AND @LargeChainsOrTushev = 0)
BEGIN
 SELECT DISTINCT * FROM #TempFinal WHERE ZFlag = 0 AND ExFlag = 0
END
ELSE IF @ZeroPercent = 1 AND @LargeChainsOrTushev = 0
BEGIN
 SELECT DISTINCT * FROM #TempFinal WHERE ZFlag = 1 AND ExFlag = 0
END
ELSE IF @ZeroPercent = 0 AND @LargeChainsOrTushev = 1
BEGIN
 SELECT DISTINCT * FROM #TempFinal WHERE ZFlag = 0 AND ExFlag = 1
END
ELSE
BEGIN
 SELECT DISTINCT * FROM #TempFinal
END

-- 清理临时表（可选，SQL Server会在会话结束时自动删除）
DROP TABLE IF EXISTS #TempFinal;

4. 另一种可选方案 #

If you prefer not to use a temporary table, you can wrap your entire IF block in a single executable statement using EXEC(), but the temporary table approach is generally more readable and easier to maintain for this scenario.

内容的提问来源于stack exchange，提问作者Stoycho Dimitrov