I can't use my declare @AverageValue in SQL Server

Question

I have a variable in SQL Server. It's name is @AverageValue.

EXEC sp_executesql @DynamicSQL, N'@AverageValue FLOAT OUTPUT', @AverageValue OUTPUT 

In this line I can see the result and @AverageValue has a value. Like 2.1135432, it's working for me it's fine but I have to use it on other parts.

SELECT CAST(@average AS FLOAT) AS OutputMessage

IF @AverageValue > 1
BEGIN
    SET @isLowAverage = 1
END

On this line @AverageValue is null. So I need to use @AverageValue's value but when it comes null I can't set @isLowAverage. But it's not null when I EXEC it. What is the problem? Here's my stored procedure:

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
        SET @DynamicSQL = 'SELECT ''' + CAST(@ParameterId AS NVARCHAR) + ''' AS ParameterId, AVG(CAST([parameterValue] AS FLOAT)) AS AverageValue FROM [SPCParametersDataT] WHERE parameterId = ' + CAST(@ParameterId AS NVARCHAR) + ' AND [date] >= ''' + CONVERT(NVARCHAR, @StartDate, 121) + ''''
        
      EXEC sp_executesql @DynamicSQL, N'@AverageValue FLOAT OUTPUT', @AverageValue OUTPUT 

        SELECT CAST(@average AS FLOAT) AS OutputMessage

        IF @AverageValue > 1
        BEGIN
            SET @isLowAverage = 1
            SET @date = GETDATE()
        END

        FETCH NEXT FROM ParameterCursor INTO @ParameterId
    END

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

I need work it on this line. I need the value but it's returned as null. But it's not null one line above

Answer 1

You need to assign the computed average value to the variable

SET @DynamicSQL = 'SELECT @AverageValue = AVG(CAST([parameterValue] AS FLOAT)) FROM [SPCParametersDataT] WHERE parameterId = ' + CAST(@ParameterId AS NVARCHAR) + ' AND [date] >= ''' + CONVERT(NVARCHAR, @StartDate, 121) + ''''

EXEC sp_executesql @DynamicSQL, N'@AverageValue FLOAT OUTPUT', @AverageValue OUTPUT 

Proper way of using Dynamic Query is to use parameters for all and not to concatenate (@ParameterId and @StartDate) into the query.

Your dynamic query should be

SET @DynamicSQL = 'SELECT @AverageValue = AVG(CAST([parameterValue] AS FLOAT)) FROM [SPCParametersDataT] WHERE parameterId = @ParameterId AND [date] >= @StartDate'

EXEC sp_executesql 
       @DynamicSQL, 
       N'@AverageValue FLOAT OUTPUT, @parameterId INT, @StartDate datetime', 
       @AverageValue OUTPUT, @parameterId, @StartDate

Actually, there isn't anything dyanamic at all in your query. You can accomplish the same with a simple select query

SELECT @AverageValue = AVG(CAST([parameterValue] AS FLOAT)) 
FROM   [SPCParametersDataT] 
WHERE  parameterId = @ParameterId 
AND    [date] >= @StartDate

Not sure what are you trying to do but you probably don't need the cursor and while loop also.