April 20, 2004 at 2:21 pm
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!!
April 21, 2004 at 2:35 am
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.
April 21, 2004 at 5:51 am
Thanks. That's what I ended up doing and it worked fine. Thanks again.
April 28, 2004 at 3:49 am
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
April 28, 2004 at 5:32 am
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