SQL Server存储过程中IIF实现动态条件过滤的问题咨询

嘿，我来帮你搞定这个存储过程的逻辑问题～你的需求是做可选参数的灵活筛选，但当前的写法有几个关键问题，导致无法正确实现需求：

原写法的核心问题 #

• 姓名匹配逻辑错误：当@name为NULL时，你用iif(@name is null, '', @name)，这会让CDT_NAME LIKE ''只能匹配姓名为空字符串的记录，而不是返回所有记录，完全违背了“无参数时返回全部”的需求。
• 年龄区间的类型错误：把NULL转成空字符串和数字类型的CDT_AGE比较，会触发隐式类型转换错误，而且就算转换成功，between '' and ''也不可能匹配任何合法的年龄值。
• 模糊匹配缺失通配符：原写法里的LIKE @name是精确匹配，不是你需要的模糊匹配哦。

正确的实现方案 #

我们需要给每个参数加上“参数为NULL时跳过该筛选条件”的逻辑，同时修复模糊匹配和年龄比较的问题，最终的查询语句如下：

SELECT CDT_ID 
FROM CANDIDATE 
WHERE 
    -- 姓名模糊筛选：参数为NULL则不限制，否则进行模糊匹配
    (@name IS NULL OR CDT_NAME LIKE '%' + @name + '%')
    -- 年龄下限筛选：参数为NULL则不限制，否则年龄≥下限
    AND (@lower_age IS NULL OR CDT_AGE >= @lower_age)
    -- 年龄上限筛选：参数为NULL则不限制，否则年龄≤上限
    AND (@upper_age IS NULL OR CDT_AGE <= @upper_age)

关键细节说明 #

1. 模糊匹配的通配符：这里用%包裹@name实现全模糊匹配，如果你的需求是前缀匹配（比如“张”开头的姓名），可以改成CDT_NAME LIKE @name + '%'；如果是后缀匹配，改成CDT_NAME LIKE '%' + @name即可。
2. 年龄区间的灵活处理：用>=和<=代替BETWEEN，可以单独处理上下限参数（比如只传最小年龄，或者只传最大年龄），逻辑更清晰。
3. 通配符转义（可选）：如果@name参数可能包含SQL通配符（比如%、_），可以用REPLACE和ESCAPE避免意外匹配，示例：

   CDT_NAME LIKE '%' + REPLACE(REPLACE(@name, '%', '[%]'), '_', '[_]') + '%' ESCAPE '['
   

4. 性能优化：如果CANDIDATE表数据量较大，可以给CDT_NAME建立全文索引（针对模糊匹配），给CDT_AGE建立普通索引，提升查询效率。

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