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 15:38:34 -0400

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

  • Re: UDF and SQL2000 - Why doesnt this work?
    ... Basically This is what I want to do - I have created a query ... > 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)
  • loop through the current year date
    ... how would I write it to loop ... DECLARE @Start DATETIME ... select countas [March FNA] from patientclinical ...
    (microsoft.public.sqlserver.programming)
  • Re: Spalten alternativ ansprechen
    ... DECLARE @Monat INT ... '01' AS DATETIME) ... case when DATUM between '...' ... then UMSATZ as ...
    (microsoft.public.de.access.clientserver)
  • Re: a view based on stored procedure
    ... DECLARE @Week_1 DATETIME ... DECLARE @CubeTime DATETIME ... INSERT INTO WrkAvailPlanHours ...
    (microsoft.public.sqlserver.programming)