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
1条答案
按热度按时间cbjzeqam1#
You need to assign the computed average value to the variable
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
Actually, there isn't anything
dyanamic
at all in your query. You can accomplish the same with a simple select queryNot sure what are you trying to do but you probably don't need the cursor and while loop also.