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

JDP_at_Work
Date: 08/13/04 

Date: Fri, 13 Aug 2004 13:41:34 -0700

...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
>
>
>
>
>
>
>
>
>
>
>
>

Relevant Pages

  • Speeding up call to a remote table
    ... I've got a SQL Query created to pull aggregate values from a linked ... Declare @StartDate as datetime ... Declare @EndDate as datetime ...
    (microsoft.public.sqlserver.programming)
  • Re: Bulding expressions in views
    ... If iStdtand iDateare of type datetime: ... declare @iStdt2 datetime ... query, you should store the value in a local variable whenever possible. ... WHERE (dDatez BETWEEN dbo. ...
    (microsoft.public.access.adp.sqlserver)
  • UDF and SQL2000 - Why doesnt this work?
    ... The loop only seems to run through once and then it exits returning either ... datetime, @PeriodEnd datetime) ... DECLARE @TempDate datetime ... DECLARE @TempVal float ...
    (microsoft.public.sqlserver.programming)
  • Re: UDF and SQL2000 - Why doesnt this work?
    ... assignment to the @DailyVal variable is the problem. ... loop actually works when I return a datetime and only update the date. ... DECLARE @TempDate datetime ... DECLARE @TempVal float ...
    (microsoft.public.sqlserver.programming)
  • RE: Select across databases
    ... > How would I add this to the query? ... > also in the other database. ... > DECLARE @startdate AS DATETIME ...
    (microsoft.public.sqlserver.programming)