I have this stored procedure that loops through a table and updates a
couple of fields. For some reason one of the fields is not being
updated. If I run the same code from query analyzer, it works fine.
Let me know if anyone can figure out why @lastscandate would ever be
NULL. If it is null it should be equal to @maildate. The senerio that
seems to fail is when no records are returned from the select statement
to fill in @lastscandate. This should then active the next if
statement and set the @lastscandate equal to the @maildate. MailDate
is always filled in in the database and LastScanDate will be NULL.
Thanks for your help.
DECLARE c1 CURSOR LOCAL FOR
SELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)
WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid
= 0) AND (m.maildate ='' OR m.maildate IS NULL)
AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)
WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycle
AND p.status NOT IN ('BM', 'PM'))
OPEN c1
FETCH FROM c1 INTO @mid, @acctno, @ordid, @cycle
WHILE @@fetch_status = 0
BEGIN
--Get MailDate from Manifest - if NULL then use GetDate
set @maildate = NULL
SELECT @maildate = MAX(whenmailed) FROM manifest WITH (nolock)
WHERE acctno = @acctno AND ordid = @ordid AND cycle = @cycle
if @maildate is NULL
set @maildate = getdate()
--Get Last Scan Date from Transactions - if NULL then use MailDate
set @lastscandate = NULL
select @lastscandate=max(actiondate) from transactions where
acctno=@acctno and ordid=@ordid and cycle=@cycle and actionid=303
if @lastscandate is NULL
set @lastscandate = @maildate
BEGIN TRANSACTION
UPDATE master SET printstatus = 'ML', maildate = @maildate,
lastscandate=@lastscandate
WHERE id = @mid
INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program) VALUES ('RLT', 55, 'Mars', @acctno, @ordid,
@cycle, 'Update Mail Dates')
COMMIT TRANSACTION
FETCH NEXT FROM c1 INTO @mid, @acctno, @ordid, @cycle
END
CLOSE c1
