Re: UDF and SQL2000 - Why doesn't this work?
From: Calvin X (freerobotno_spammingdrone_at_rotsnail.com)
Date: 08/13/04
- Next message: Calvin X: "Re: UDF and SQL2000 - Why doesn't this work?"
- Previous message: Andrew J. Kelly: "Re: Alter Procedure"
- In reply to: Calvin X: "UDF and SQL2000 - Why doesn't this work?"
- Next in thread: Hugo Kornelis: "Re: UDF and SQL2000 - Why doesn't this work?"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 13 Aug 2004 16:48:04 -0400
Ok I have done some experimenting and have come to the conclusion that the
assignment to the @DailyVal variable is the problem. I have revised my
function and have listed it here for all to see and make comments on. The
loop actually works when I return a datetime and only update the date.
CREATE FUNCTION dbo.SumVarForPeriod (@Var nvarchar(50), @PeriodStart
datetime, @PeriodEnd datetime)
RETURNS float
AS
BEGIN
DECLARE @CurDate datetime
DECLARE @TempDate datetime
DECLARE @TempVal float
DECLARE @Total float
DECLARE @DailyVal float
-- loop through the data adding the values until we are over the end date
SELECT @CurDate = DateAdd(day,1,@PeriodStart)
WHILE @CurDate <= @PeriodEnd
BEGIN
-- the date is being updated properly but the result is null most often or
only one of the values not a sum of them
SELECT @DailyVal = (SELECT DailyVal FROM qryUtVariablesDailyVal WHERE
VariableCode=@Var AND StartDate < @CurDate AND EndDate >= @CurDate)
if isnull(@DailyVal,0) = 0
SET @DailyVal = 0
SET @Total = @DailyVal
BREAK
-- perhaps the problem is down here in the assignment section?
SET @TempVal = @Total + @DailyVal
SET @Total = @TempVal
SET @TempDate = DateAdd(day,1,@CurDate)
SET @CurDate = @TempDate
END
RETURN @Total
END
- Next message: Calvin X: "Re: UDF and SQL2000 - Why doesn't this work?"
- Previous message: Andrew J. Kelly: "Re: Alter Procedure"
- In reply to: Calvin X: "UDF and SQL2000 - Why doesn't this work?"
- Next in thread: Hugo Kornelis: "Re: UDF and SQL2000 - Why doesn't this work?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
