Azure SQL中带变量与硬编码参数的UDF查询结果不一致问题

问题现象 #

• 用变量传递参数调用udf_a时触发除零错误，附带聚合消除NULL值的警告，执行耗时6.52秒：

  DECLARE @year INT = 2024
  DECLARE @month INT = 7
  SELECT *
  FROM udf_a(@year, @month)
  

  Msg 8134, Level 16, State 1, Line 3
  Divide by zero error encountered.
  Warning: Null value is eliminated by an aggregate or other SET operation.
  Total execution time: 00:00:06.520

• 直接传入常量参数时，函数正常返回预期结果集：

  SELECT *
  FROM udf_a(2024, 7)
  

  返回包含预期数据的结果集

环境信息 #

• SQL版本：Microsoft SQL Azure (RTM) - 12.0.2000.8
• 操作工具：Azure Data Studio v1.48.1
• 函数说明：udf_a是内联表值函数，内部会调用同参数的表值函数udf_b，函数结构如下：

  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER FUNCTION [dbo].[udf_a](@year INT, @month INT)
  RETURNS TABLE AS
  RETURN
      SELECT *
      FROM BLAH
      WHERE BLAH = BLAH
  GO
  

原因分析 #

这种差异源于SQL Azure对常量参数和变量参数的查询优化逻辑差异：

1. 传入常量时，查询优化器会提前做常量折叠，结合udf_b内部的过滤、聚合逻辑，自动跳过那些会触发除零错误的数据集，生成更精准的执行计划。
2. 传入变量时，优化器无法提前确定变量值，会生成通用执行计划，导致原本在常量场景下被过滤的行被纳入计算，触发除零错误。

解决方案 #

1. 强制重新编译（快速临时解决） #

在变量参数的查询语句末尾添加OPTION(RECOMPILE)，让优化器基于当前变量值生成针对性执行计划：

DECLARE @year INT = 2024
DECLARE @month INT = 7
SELECT *
FROM udf_a(@year, @month)
OPTION(RECOMPILE)

2. 修复udf_b的除零逻辑（根本解决） #

找到udf_b中存在除法运算的地方，添加防除零判断，比如用NULLIF将除数为0的情况转为NULL：

-- 原错误写法示例：
-- col1 / col2

-- 修改后：
col1 / NULLIF(col2, 0)

或者用CASE语句更明确控制逻辑：

CASE WHEN col2 <> 0 THEN col1 / col2 ELSE NULL END

3. 动态SQL模拟常量传入 #

通过动态SQL拼接常量值执行，模拟直接传常量的优化效果：

DECLARE @year INT = 2024
DECLARE @month INT = 7
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM udf_a(' + CAST(@year AS NVARCHAR(4)) + N', ' + CAST(@month AS NVARCHAR(2)) + N')'
EXEC sp_executesql @sql

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