SQL Server中@AverageValue变量执行后值为空问题求助

问题根源 #

1. 动态SQL未绑定输出参数：你的动态SQL仅返回了包含平均值的结果集，但没有将计算出的AverageValue赋值给sp_executesql声明的输出参数@AverageValue，导致外部变量无法获取到值。
2. 变量名误用：后续SELECT CAST(@average AS FLOAT)中的@average是单独声明的nvarchar变量，从未被赋值，自然返回NULL；而实际需要使用的是@AverageValue。
3. SQL注入风险与格式问题：直接拼接@ParameterId和@StartDate到动态SQL中，不仅存在注入风险，日期格式拼接还可能因环境设置引发错误。

修正后的存储过程代码 #

AS
BEGIN
    DECLARE @StartDate DATETIME = DATEADD(HOUR, -24, GETDATE())
    DECLARE @ParameterIds NVARCHAR(100) = '2246,2247'
    DECLARE @isLowAverage BIT = 0
    DECLARE @sendMessage bit = 0,
            @toMailAddresses nvarchar(MAX)='',  
            @mailSubject nvarchar(MAX)='',  
            @mailBody nvarchar(MAX)='',
            @roleId int = 22222222,
            @tableHtml nvarchar(max),
            @departman nvarchar(max),
            @location nvarchar(max),
            @date nvarchar(max),
            @average nvarchar(max), -- 可删除，未实际用到
            @AverageValue FLOAT

    DECLARE @ParameterTable TABLE (ParameterId INT)

    INSERT INTO @ParameterTable
        SELECT CAST(value AS INT) AS ParameterId
        FROM STRING_SPLIT(@ParameterIds, ',')

    DECLARE @ParameterId INT
    DECLARE @DynamicSQL NVARCHAR(MAX)

    DECLARE ParameterCursor CURSOR FOR
        SELECT ParameterId FROM @ParameterTable

    OPEN ParameterCursor
    FETCH NEXT FROM ParameterCursor INTO @ParameterId

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 修正动态SQL：将平均值赋值给输出参数，同时参数化查询条件避免注入
        SET @DynamicSQL = N'SELECT @AverageValue = AVG(CAST([parameterValue] AS FLOAT)) 
                            FROM [SPCParametersDataT] 
                            WHERE parameterId = @ParamId 
                              AND [date] >= @StartDt'
        
        -- 使用参数化传递输入参数，同时绑定输出参数
        EXEC sp_executesql @DynamicSQL, 
                           N'@ParamId INT, @StartDt DATETIME, @AverageValue FLOAT OUTPUT',
                           @ParamId = @ParameterId,
                           @StartDt = @StartDate,
                           @AverageValue = @AverageValue OUTPUT 

        -- 修正变量名，使用@AverageValue而非未赋值的@average
        SELECT @AverageValue AS OutputMessage

        IF @AverageValue > 1
        BEGIN
            SET @isLowAverage = 1
            SET @date = CONVERT(NVARCHAR, GETDATE(), 121) -- 建议统一日期格式
        END

        FETCH NEXT FROM ParameterCursor INTO @ParameterId
    END

    CLOSE ParameterCursor
    DEALLOCATE ParameterCursor
  
    SELECT 'isLowAverage: ' + CAST(@isLowAverage AS NVARCHAR) AS OutputMessage
END

关键修正点说明 #

• 动态SQL中使用SELECT @AverageValue = AVG(...)将计算结果直接赋值给输出参数，确保sp_executesql能把值传递到外部变量。
• 将@ParameterId和@StartDate改为参数化传递，彻底避免SQL注入风险，同时解决日期格式拼接的潜在问题。
• 修正了变量名错误，后续查询和判断均使用正确的@AverageValue变量。

内容的提问来源于stack exchange，提问作者M.Eren