SQL Server查询手动正常但定时调度报错：未声明标量变量

问题背景 #

编写的报表SQL在SSMS和Trimble/3LOG LIMS GUI手动运行正常，但定时调度时抛出错误：[FireDAC][Phys][ODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server] 必须声明标量变量 "@L_LB_TO_TON"，推测是第三方应用将定时查询分批次执行，导致变量声明超出作用域。

解决方案 #

方案1：直接硬编码常量值 #

去掉变量声明，将转换系数直接写入CASE表达式中，彻底避免变量作用域问题。注意保持类型匹配，确保计算精度与原逻辑一致：

SELECT
    l.other_values,
    v.other_values,
    t.other_values,
    (CASE 
         WHEN l.scale_uom = 'LB' 
             THEN CAST(0.0005 * CAST(l.net AS BIGINT) AS DECIMAL(12,4))
         WHEN l.scale_uom = 'KG'
             THEN CAST(0.00110231 * CAST(l.net AS BIGINT) AS DECIMAL(12,4))
         ELSE NULL
     END) AS net_tons
FROM
    table l
LEFT JOIN 
    table2 c ON c.value = l.value
LEFT JOIN 
    table3 v ON v.value = c.value
LEFT JOIN 
    table4 wl ON wl.value = l.value 
              AND wl.value2 = l.value2
LEFT JOIN 
    table5 t ON t.value = l.trip_value
WHERE 
    stuff_is_true
ORDER BY
    something

方案2：用子查询定义常量并交叉连接 #

通过子查询封装转换系数，再与主查询交叉连接，既保留常量的可维护性，又避开DECLARE语句的作用域限制：

SELECT
    l.other_values,
    v.other_values,
    t.other_values,
    (CASE 
         WHEN l.scale_uom = 'LB' 
             THEN CAST(constants.L_LB_TO_TON * CAST(l.net AS BIGINT) AS DECIMAL(12,4))
         WHEN l.scale_uom = 'KG'
             THEN CAST(constants.L_KG_TO_TON * CAST(l.net AS BIGINT) AS DECIMAL(12,4))
         ELSE NULL
     END) AS net_tons
FROM
    table l
CROSS JOIN (
    SELECT 
        CAST(0.0005 AS DECIMAL(5,4)) AS L_LB_TO_TON,
        CAST(0.00110231 AS DECIMAL(9,8)) AS L_KG_TO_TON
) AS constants
LEFT JOIN 
    table2 c ON c.value = l.value
LEFT JOIN 
    table3 v ON v.value = c.value
LEFT JOIN 
    table4 wl ON wl.value = l.value 
              AND wl.value2 = l.value2
LEFT JOIN 
    table5 t ON t.value = l.trip_value
WHERE 
    stuff_is_true
ORDER BY
    something

方案3：修正CTE语法（若应用支持CTE） #

如果之前使用CTE报错，大概率是语法细节问题，确保CTE前无未闭合语句（或强制以分号开头），并通过交叉连接关联常量：

;WITH constants AS (
    SELECT 
        CAST(0.0005 AS DECIMAL(5,4)) AS L_LB_TO_TON,
        CAST(0.00110231 AS DECIMAL(9,8)) AS L_KG_TO_TON
)
SELECT
    l.other_values,
    v.other_values,
    t.other_values,
    (CASE 
         WHEN l.scale_uom = 'LB' 
             THEN CAST(constants.L_LB_TO_TON * CAST(l.net AS BIGINT) AS DECIMAL(12,4))
         WHEN l.scale_uom = 'KG'
             THEN CAST(constants.L_KG_TO_TON * CAST(l.net AS BIGINT) AS DECIMAL(12,4))
         ELSE NULL
     END) AS net_tons
FROM
    table l
CROSS JOIN constants
LEFT JOIN 
    table2 c ON c.value = l.value
LEFT JOIN 
    table3 v ON v.value = c.value
LEFT JOIN 
    table4 wl ON wl.value = l.value 
              AND wl.value2 = l.value2
LEFT JOIN 
    table5 t ON t.value = l.trip_value
WHERE 
    stuff_is_true
ORDER BY
    something

注意事项 #

• 若硬编码时出现关联错误，检查计算式的类型转换是否与原变量精度一致（比如DECIMAL(5,4)和DECIMAL(9,8)的精度要求）
• 确认第三方应用的SQL执行规则，避免单个查询被拆分为多个批次执行（比如DECLARE和SELECT被分开处理）

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