Continue Updates after encountering an error in a Cursor

[pabowen]

I have a stored procedure that updates 1000 to 50000 records at a time (depending on dbase traffic) by iterating through a cursor on a subset table. Each line is processed by a second stored procedure that updates the corresponding row in the main table.

I am encountering an issue where occasionally I encounter an error on the update (unique index violation, truncated field, etc.) I need to find a way that if the cursor hits an error it will continue on to the next row and not stop updating all other records. Currently it completely stops after the first error. This is a scheduled job in SQL Agent.

The second item I would like to do is return the error to a log table, so it can be monitored and fixed.

I appreciate any help, direction, or laughs you may provide

P.A. Bowen

 

[SQLSister]

First things first. Using a cusor to do an update is not a good practice. It is very slow and will get slower as your tba;le gets larger. You need to replace it with a set-based update statement if possible. Tell us what you are doing in the cursor and we will try to help you write a better update.

Second, you can set up the the stored porcedure to test for the kinds of errors you get before you run the update statement and use the results of the query to update an error table. I don't know how you could stop a cursor from failing on these errors though as a cusor must, by definition, go through all rows. The better choice is to set up your user interface and tables as to not allow these bad data entries in the first place.

Third, rather than periodically running the process to update 50000 records, why not put in a trigger to update every record as it is entered? Obviously this is not possible if this is a linked table of some kind (like an Access table) which doesn't allow triggers or if you are doing a data import (but in this case it could be part of the DTS package). But it is something to think about.

 

[pabowen]

SQLSister, thanks for the reply. Unfortuneately a cursor is the only way I can find to perform the action I need. I am connecting a MS SQL 2000 dbase to a remote DB2 dbase that I only have select permissions on a set of views.

I am forced to perform openquery's over a linked connectin

I also have no control over data quality on the db2 end.

This is all performed via DTS, The dbase I am working on is a mission critical app that must be as close to in real time sync as possible with the DB2 system.

Every 20 minutes, I am performing an openquery select and populate a local table 'ivueupdate' based on the column update timestamp in DB2. This select gives me 2 columns (CaseNo, ClaimNo) of records that have been updated since during the select period. I then clean up the data the best I can.

I iterate throught the cursor and submit the claimno to the stored procedure which returns the values I need to update the record in my database. This is necessary do to very complex logic in calculating some of the fields required.

declare @claim_nbr varchar(30)
declare cur insensitive cursor for
select ClaimNo
from IvueUpdate
open cur
while (1=1)
begin
fetch next from cur into @claim_nbr
if (@@FETCH_STATUS <> 0)
break
exec sp_ivUpdateAcquire @claim_nbr
end

close cur
deallocate cur
GO

Once I have updated the records the ivueupdate table is deleted so it only holds data for the current update period. It is very rare that I update 50000 at a time, typical would be less then 4,000.

I just need to make sure that it doesn't fail if it does encounter an error.

 

[SQLSister]

Best bet is to do the data cleanup before you run the cursor. Write queries to check for errors and if they are there to move the record to another table and delete from the table you will run the cursor on.
Suppose your CaseNo field is a field which must be unique. Try something like the following (assume Table1 is the base table that you are checking to see if the item already exists and table 2 is the error table):

If Exists (Select ivueupdate.CaseNo from ivueupdate inner join table1 on ivueupdate.CaseNo = table1.CaseNo)
Begin
Insert Table2 (CaseNo, ClaimNo, ReasonCode)
Select ivueupdate.CaseNo, ivueupdate.ClaimNo, 'Case No exists' from ivueupdate inner join table1 on ivueupdate.CaseNo = table1.CaseNo
Delete ivueupdate Where CaseNo IN
(Select ivueupdate.CaseNo from ivueupdate inner join table1 on ivueupdate.CaseNo = table1.CaseNo)
End

I didn't test this, so my syntax may not be exactly correct, but you get the idea.