Using Cursor Help

  • Hello. I am trying to run a sql statement using a cursor. Checked the syntax and in QA and doesn't give me any errors. Tried to run the statement and comes back with a message stating that I need to declare one of variables, which is already done at the begining of the statement.

    Here is the SQL Statement and hope someone can give any light of what I am doing wrong. Thank you in advance!!

    /* Variables that table info is read into. */

    DECLARE @StartDate datetime

    DECLARE @NbrOfPeriods int

    DECLARE @DateDiff char(8)

    DECLARE @StepPmtAmt Money

    /* Calculation Variables */

    DECLARE @TotPmt Money

    DECLARE @PeriodCount int, @DDCount int, @DDPeriodType char(2)

    DECLARE @CalcDate Datetime

    DECLARE @YearMonthIn char(6)

    DECLARE @CaseNo char(7)

    DECLARE @YearIn char(4)

    DECLARE @MonthIn char(2)

    SET @CaseNo = '0023048'

    SET @YearIn = '2004'

    SET @MonthIn = '05'

    SET @YearMonthIn = CONVERT(char(4),@YearIn)+CONVERT(char(2),@MonthIn)

    SET @CalcDate = @StartDate

    DECLARE StepCursor Cursor for

        SELECT  tblCaseSteps.StartDate, tblCaseSteps.NumberOfPeriods, tblCasePayScheduleTypes.[DateDiff], tblCaseSteps.PaymentAmount

        FROM    tblCasePayScheduleTypes INNER JOIN

                tblCaseSteps ON tblCasePayScheduleTypes.CasePaySchedCodeID = tblCaseSteps.CasePaySchedCodeID INNER JOIN

                vwMainCase ON tblCaseSteps.CaseID = vwMainCase.CaseID

        WHERE  (vwMainCase.CaseNumber = @CaseNo) and

               (@YearMonthIn >= CONVERT(char(4),YEAR(tblCaseSteps.StartDate))+

                CONVERT(char(2),MONTH(tblCaseSteps.StartDate)))

    OPEN StepCursor

    FETCH NEXT FROM StepCursor into @StartDate, @NbrOfPeriods, @DateDiff, @StepPmtAmt WHILE @@FETCH_STATUS = 0

    BEGIN

        SET @PeriodCount = @NbrOfPeriods -1

        SET @CalcDate = @StartDate

        IF (@MonthIn = CONVERT(char(2),MONTH(@CalcDate))) AND (@YearIn = CONVERT(char(4),YEAR(@CalcDate)))

            SET @TotPmt = @TotPmt + @StepPmtAmt

        SET @DDPeriodType = rtrim(LEFT(@DateDiff,2))

    select @ddperiodtype, @periodCount, @nbrofperiods

        WHILE @PeriodCount > 0

        BEGIN

            SET @DDCount = CONVERT(int,rtrim(SUBSTRING(@datediff,3,8)))

            WHILE @DDCount > 0

            BEGIN

                EXEC ('SET @CalcDate = DATEADD(' + @DDPeriodType + ',1,@CalcDate)')

                SET @DDCount = @DDCount - 1

            END

            IF (@MonthIn = CONVERT(char(2),MONTH(@CalcDate))) AND (@YearIn = CONVERT(char(4),YEAR(@CalcDate)))

                SET @TotPmt = @TotPmt + @StepPmtAmt

            IF @YearMonthIn < CONVERT(char(4),YEAR(@CalcDate))+CONVERT(char(2),MONTH(@CalcDate))

                SET @PeriodCount = 0

            ELSE SET @PeriodCount = @PeriodCount - 1

        END

        FETCH NEXT FROM StepCursor into @StartDate, @NbrOfPeriods, @DateDiff, @StepPmtAmt END close StepCursor deallocate StepCursor Select @TotPmt

    The message I am getting reads:

    1 row(s) affected)

    Server: Msg 137, Level 15, State 2, Line 1

    Must declare the variable '@CalcDate'.

    Server: Msg 137, Level 15, State 2, Line 1

    Must declare the variable '@CalcDate'.

    and repeats the MUST DECLARE quite a few times. Thanks again!!

     

  • I believe that this is the offending line :

    EXEC ('SET @CalcDate = DATEADD(' + @DDPeriodType + ',1,@CalcDate)')

    because you're 'exec'ing this statement, it doesn't have visibility of your previously declared variable @CalcDate.

    You might consider replacing this with a CASE statement, dependent on the value of @DDPeriodType. Since this can take only take a limited number of values, the CASE statement should be very straightforward.

  • Thanks. That's what I ended up doing and it worked fine. Thanks again.

  • Cmartinz,

    Would you mind posting your revised code? I'm having the same problem and looking for some answers. Not exactly clear on how you formed the CASE statement as a workaround?

    Thanks.

     

    Denise White

     

  • Denise,

    This is the revised code. Problably the first part of the statement won't help because it is calling fields from local views and tables, but the WHERE section is the one calling CASE. The developers created a field DateDiff (why???? why they use the name of the already existing function??????) so, don't get thrown out by that name.

    Hope this helps. Good luck

     

    SELECT     vwMainCase.CaseID, vwMainCase.TruCode, vwMainCase.CaseNumber, vwMainCase.DisplayCaseNumber,

               vwMainCase.TrusteeID, vwMainCase.TrusteeName, vwMainCase.Deb1LongName, vwMainCase.Deb1FirstName,

               vwMainCase.Deb1LastName, vwMainCase.Deb1AddressLine1, vwMainCase.Deb1AddressLine2, vwMainCase.Deb1AddressLine3,

               vwMainCase.Deb1City, vwMainCase.Deb1State, vwMainCase.Deb1ZipCode, vwMainCase.Deb1SSN,

               vwMainCase.Deb2LongName, vwMainCase.Deb2FirstName, vwMainCase.Deb2LastName, vwMainCase.Deb2AddressLine1,

               vwMainCase.Deb2AddressLine2, vwMainCase.Deb2AddressLine3, vwMainCase.Deb2City, vwMainCase.Deb2State,

               vwMainCase.Deb2ZipCode, vwMainCase.Deb2SSN, tblCaseName.NameTypeID AS DebNameTypeID,

               tblCaseName_1.NameTypeID AS PayeeNameTypeID, tblName.LongName AS PayeeLongName, tblName.AddressLine1 AS PayeeAddressLine1,

               tblName.AddressLine2 AS PayeeAddressLine2, tblName.AddressLine3 AS PayeeAddressLine3, tblName.City AS PayeeCity,

               tblName.State AS PayeeState, tblName.ZipCode AS PayeeZipCode, tblCaseSteps.StartDate, tblCaseSteps.NumberOfPeriods,

               tblCasePayScheduleTypes.DateDiff, tblCaseSteps.PaymentAmount

    FROM       tblCasePayScheduleTypes INNER JOIN

               tblCaseSteps ON tblCasePayScheduleTypes.CasePaySchedCodeID = tblCaseSteps.CasePaySchedCodeID INNER JOIN

               tblCaseName ON tblCaseSteps.DebtorCaseNameID = tblCaseName.CaseNameID INNER JOIN

               tblCaseName tblCaseName_1 ON tblCaseSteps.PayingCaseNameID = tblCaseName_1.CaseNameID INNER JOIN

               tblName ON tblCaseName_1.NameID = tblName.NameID INNER JOIN

               vwMainCase ON tblCaseSteps.CaseID = vwMainCase.CaseID

    WHERE      (tblCaseName_1.NameTypeID = 7)  AND

               (vwMainCase.CaseNumber = @CaseNo) AND

               (tblCaseSteps.StartDate > getdate()) AND

               (tblCaseSteps.NumberOfPeriods >=

               CASE

                   WHEN LEFT(tblCasePayScheduleTypes.DateDiff,2))='ww' then DATEDIFF(ww,tblCaseSteps.StartDate, GETDATE())

                   WHEN LEFT(tblCasePayScheduleTypes.DateDiff,2))='m ' then DATEDIFF(m,tblCaseSteps.StartDate, GETDATE())

                   WHEN LEFT(tblCasePayScheduleTypes.DateDiff,2))='y ' then DATEDIFF(y,tblCaseSteps.StartDate, GETDATE())

                   WHEN LEFT(tblCasePayScheduleTypes.DateDiff,2))='d ' then DATEDIFF(d,tblCaseSteps.StartDate, GETDATE())

                   WHEN LEFT(tblCasePayScheduleTypes.DateDiff,2))='q ' then DATEDIFF(q,tblCaseSteps.StartDate, GETDATE())

               END

               / CAST(RTRIM(SUBSTRING(tblCasePayScheduleTypes.DateDiff, 3, 8)) AS float))

    GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply