Hi everyone!
I am trying to use error handling within a cursor routine. I have an Error Handling label outside the cursor that handles errors generated outside the cursor or when @@Fetch <> 0, but doesn't work for capturing errors inside the cursor routine. The cursor has a value that I am using for updating data. This requires a transaction for each pass. I also must select the max value of a selected field from the table where these updates occur. If an error occurs in any part of the transaction, I want to capture the error, select it from the sysmessages table, write it to a table, roll back the transaction and move to the next value in the cursor. I can create separate error handling for each update or append in the transaction, but that seems overly redundant.
DECLARE MyCursor CURSOR Static FOR
SELECT MyRecord
FROM MyTable
OPEN MyCursor
DECLARE @NewRecord int,
WHILE @@Fetch = 0
BEGIN
FETCH NEXT
FROM MyCursor INTO @NewRecord
BEGIN TRAN
UPDATE MyTable1
SET MyValue = ''
where RecordID = @NewRecord
---How to process if error occurs for each update
COMMIT TRAN
Continue
END
END
CLOSE MyCursor
DEALLOCATE MyCursor
RETURN
GO
Using error handling techniques in cursor routine
bblackshaw
Thanks,
This how I normally handle errors with standard set based operations except I use an error handling label and a goto statement so I only have to create this code once. Unfortunately I can not use this type of error handling inside of the cursor routine. My only option at this point is to place the complete set of error handling on each update/append inside of the transaction. Because there are a large number of these updates/appends, this will create long, difficult to read/troubleshoot SQL
AMK
Thanks. I looked at these and several other possibilites and none appeared to be useful. I may end up handling any error that occurs for each update or append in the transaction similar to my standard error handling routine. It makes the SQL long and difficult to follow, but for a lack of a better answer that may be my solution.
I avoid cursor routines any way I can. But, as I explained in my response to another post, I have issues that prevent me from using set based operations.
Libelula
You want to periodically check @@error after you perform each statement that is likely to fail. Then use an if statement, IF @@error = 0 then do error logic, else do nothing and go to next record. Thus:
declare @err int
declare @errormessage varchar(510)
set @err = 0
BEGIN TRAN
UPDATE MyTable1
SET MyValue = ''
where RecordID = @NewRecord
set @err = @@error
IF @err <> 0
BEGIN
ROLLBACK TRAN
INSERT INTO some table
SELECT @errormessage = description
FROM master..sysmessages WHERE error = @err
END
COMMIT TRAN
FranckDG
I aggree, unless you have a good reason for using a cursor, doing this in a full update statement would be much better. However, you seem to have a good grasp of SQL so I am presuming you are using a cursor for good reason.
There is no way I know of to do a try .. catch style statement that catches an error that occurs anywhere in a stored procedure, unfortunately you have to check for an error after every statement that could throw one. You could use SET XACT_ABORT ON, which causes all transactions to abort when an error is thrown. However, you would still have to restart the loop, as it were, as all the changes made would be rolled back. There is no way I am aware of to catch an error and continue with the next line of code in SQL server. You can obviously do this in client side code though, so you may be better using datasets on the client side to handle the data updating. I have come across this problem before, and it is frustrating.
amosz42
For more SQL tips, visit my blog:
http://blogs.claritycon.com/blogs/the_englishman/default.aspx
HTH
Sven V
Sunilk_007