Re: UDF and SQL2000 - Why doesn't this work?

From: Calvin X (freerobotno_spammingdrone_at_rotsnail.com)
Date: 08/13/04 

Date: Fri, 13 Aug 2004 16:48:30 -0400

I left that in by accident...
"JDP@Work" <JPGMTNoSpam@sbcglobal.net> wrote in message
news:OFcHrWXgEHA.3932@TK2MSFTNGP09.phx.gbl...
> ...not an answer, but do you init your total var inside your while?
>
> TIA
>
> JeffP....
>
> "Calvin X" <spam freerobotno_spammingdrone@rotsnail.com> wrote in message
> news:uVAcg0WgEHA.636@TK2MSFTNGP12.phx.gbl...
> > Hi Everyone,
> > I am having some problems getting this user defined function to return
data
> > that I need. Basically This is what I want to do - I have created a
query
> > that breaks down values for a period start and end into a daily value
and I
> > want to loop through these values and sum the total value based on the
> > period that is passed to the function. The period that is passed is
most
> > ofter quite different than the period that is defined for the query
values
> > that are being broken into daily values.
> >
> > The loop only seems to run through once and then it exits returning
either
> > NULL or the correct value for one day. When it returns null I run the
same
> > query against the data source and it gives me the value I need.
> >
> > Ay help would be great thanks,
> >
> > Calvin X
> >
> > 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
> > IF isnull(@Var,'X') = 'X' RETURN 0
> >
> > SELECT @CurDate = DateAdd(day,1,@PeriodStart)
> >
> > WHILE @CurDate <= @PeriodEnd
> > BEGIN
> >
> > SET @Total = 0
> > SELECT @DailyVal = (SELECT DailyVal FROM qryUtVariablesDailyVal
WHERE
> > VariableCode=@Var AND StartDate < @CurDate AND EndDate >= @CurDate)
> >
> > SELECT @TempVal = @Total + @DailyVal
> > SELECT @Total = @TempVal
> > SELECT @TempDate = DateAdd(day,1,@CurDate)
> > SELECT @CurDate = @TempDate
> > END
> > RETURN @Total
> >
> > END
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>