SQL中基于双参数变量实现两Datetime列按规则区间匹配的查询问题

问题排查点 #

• 先确认@XRENTSTART、@XRENTEND两个变量已经被正确赋值，NULL值参与比较会导致条件不成立，筛选不到任何数据。
• 如果你是在SQL Server的UDF中编写该逻辑，直接调用GETDATE()会报错，因为UDF不允许使用非确定性系统函数，需要把当前时间作为参数传入UDF使用。

解决方案 #

1. 简化条件写法（推荐，逻辑更清晰）

把原来的OR分支用CASE语句改写，提取公共条件，避免冗余：

-- 如果你是在UDF中使用，请将GETDATE()替换为你传入的当前时间参数
SELECT * 
FROM [TABLE] -- TABLE是SQL保留关键字，建议加中括号转义
WHERE RENTSTARTDATE >= @XRENTSTART
AND RENTENDDATE <= CASE @XDATETYPE
    WHEN N'CUSTOMRENTRANGE' THEN @XRENTEND
    WHEN N'CURRENTRENTRANGE' THEN GETDATE()
END

2. 分支查询写法（适合大数据量场景，执行计划更优）

如果表数据量较大，拆分IF分支可以让每个查询生成对应的最优执行计划，避免参数嗅探导致的性能问题：

IF @XDATETYPE = N'CUSTOMRENTRANGE'
BEGIN
    SELECT * FROM [TABLE]
    WHERE RENTSTARTDATE >= @XRENTSTART 
    AND RENTENDDATE <= @XRENTEND
END
ELSE IF @XDATETYPE = N'CURRENTRENTRANGE'
BEGIN
    -- 如果你是在UDF中使用，请将GETDATE()替换为你传入的当前时间参数
    SELECT * FROM [TABLE]
    WHERE RENTSTARTDATE >= @XRENTSTART 
    AND RENTENDDATE <= GETDATE()
END

3. 原有写法的修复

如果要保留你原来的OR写法，只需要加上OPTION(RECOMPILE)提示，避免执行计划选错：

SELECT * 
FROM [TABLE] 
WHERE (
         (@XDATETYPE = N'CUSTOMRENTRANGE'
         AND (RENTSTARTDATE >= @XRENTSTART AND RENTENDDATE <= @XRENTEND))
         OR 
         (@XDATETYPE = N'CURRENTRENTRANGE'
         AND (RENTSTARTDATE >= @XRENTSTART AND RENTENDDATE <= GETDATE()))
      ) 
OPTION(RECOMPILE)

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