Field not being updated within Stored Procedure

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

re: Field not being updated within Stored Procedure

[posted and mailed, please reply in news]

AS400 Guru (hazen@candid.com) writes:[color=blue]
> 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.[/color]

I don't immediately see why, but I don't have the tables, so it's
difficult to debug. Since you insert into transactions and read from
it, in the same cursor, there could be some funny things.

However, I would suggest that you should rewrite as a one UPDATE
statment and one INSERT Statement. For simplicty I use a temp table
though:

INSERT #temp (...)
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'))

UPDATE master
SET printstatus = 'ML',
maildate = coalesce(mf.whenmailed, getdate(),
lastscandate = coalesce(tr.actiondate, mf.whenmailed, getdate())
FROM #temp t
JOIN master ma ON t.mid = ma.mid
JOIN (SELECT accno, ordid, cycle, whenmailed = MAX(whenmailed)
FROM manifest
GROUP BY accno, ordid, cycle) mf ON mf.ordid = t.ordid
AND mf.accntno = t.acctno
AND mf.cycle = t.cycle
JOIN (SELECT accno, ordid, cycle, actiondate = MAX(actiondate)
FROM transactions
WHERE actionid = 303
GROUP BY accno, ordid, cycle) tr ON tr.ordid = t.ordid
AND tr.accntno = t.acctno
AND tr.cycle = t.cycle

INSERT INTO transactions (initials, actionid, machinelogin, acctno,
ordid, cycle, program)
SELECT 'RLT', 55, 'Mars', acctno, ordid, cycle, 'Update Mail Dates'
FROM #temp

COMMIT TRANSACTION




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp