WHERE子句复杂条件查询报错：IF语句语法错误排查求助

你遇到的报错核心原因是SQL的流程控制语句IF不能直接放在IN子句的括号内——IN需要的是返回单列结果集的查询语句，而IF属于流程控制逻辑，无法作为IN的数据源。下面给你几种可行的修正方案：

方案一：用OR结合条件判断直接筛选 #

这种写法最直观，把每个部门对应的状态筛选逻辑用OR拼接：

DECLARE @Dept NVARCHAR(12);
SET @Dept = 'IT'

SELECT * 
FROM TBL_ORDERS 
WHERE 
    (@Dept = 'PURCH' AND ORD_STATUS IN ('PurchStat1', 'PurchStat2', 'PurchStat3', 'PurchStat4'))
    OR (@Dept = 'ADMIN' AND ORD_STATUS = 'ADStat1')
    OR (@Dept = 'IT' AND ORD_STATUS IN ('ADStat1', 'PurchStat1', 'PurchStat2', 'PurchStat3', 'PurchStat4', 'ITStat1', 'ITStat2'))

方案二：用表变量存储允许的状态 #

先根据部门把允许的状态存入表变量，再用这个表变量做筛选，适合状态列表需要后续扩展的场景：

DECLARE @Dept NVARCHAR(12);
SET @Dept = 'IT'

-- 定义表变量存储允许的状态
DECLARE @AllowedStatuses TABLE (Status NVARCHAR(50))

-- 按部门填充状态
IF @Dept = 'PURCH'
BEGIN
    INSERT INTO @AllowedStatuses (Status)
    VALUES ('PurchStat1'), ('PurchStat2'), ('PurchStat3'), ('PurchStat4')
END
ELSE IF @Dept = 'ADMIN'
BEGIN
    INSERT INTO @AllowedStatuses (Status)
    VALUES ('ADStat1')
END
ELSE IF @Dept = 'IT'
BEGIN
    INSERT INTO @AllowedStatuses (Status)
    VALUES ('ADStat1'), ('PurchStat1'), ('PurchStat2'), ('PurchStat3'), ('PurchStat4'), ('ITStat1'), ('ITStat2')
END

-- 查询符合条件的订单
SELECT * 
FROM TBL_ORDERS 
WHERE ORD_STATUS IN (SELECT Status FROM @AllowedStatuses)

方案三：用UNION ALL动态生成结果集 #

通过UNION ALL结合条件判断，动态生成IN需要的状态列表，避免流程控制语句嵌套问题：

DECLARE @Dept NVARCHAR(12);
SET @Dept = 'IT'

SELECT * 
FROM TBL_ORDERS 
WHERE ORD_STATUS IN (
    SELECT Status FROM (
        SELECT 'PurchStat1' AS Status WHERE @Dept = 'PURCH'
        UNION ALL SELECT 'PurchStat2' WHERE @Dept = 'PURCH'
        UNION ALL SELECT 'PurchStat3' WHERE @Dept = 'PURCH'
        UNION ALL SELECT 'PurchStat4' WHERE @Dept = 'PURCH'
        UNION ALL SELECT 'ADStat1' WHERE @Dept = 'ADMIN' OR @Dept = 'IT'
        UNION ALL SELECT 'ITStat1' WHERE @Dept = 'IT'
        UNION ALL SELECT 'ITStat2' WHERE @Dept = 'IT'
    ) AS AllowedStatuses
)

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