Showing posts with label handle. Show all posts
Showing posts with label handle. Show all posts

Wednesday, March 7, 2012

How can I handle OnError event in my stored proc.

Dear All:
I want to ask how can I handle OnError events in stored procedure in MSSQL.
Actually I wanted to place some Rollback procedure on this.
Can you suggest some methods for me?
KEVINfrom BOL

C. Use @.@.ERROR to check the success of several statements
This example depends on the successful operation of the INSERT and DELETE statements. Local variables are set to the value of @.@.ERROR after both statements and are used in a shared error-handling routine for the operation.

USE pubs
GO
DECLARE @.del_error int, @.ins_error int
-- Start a transaction.
BEGIN TRAN

-- Execute the DELETE statement.
DELETE authors
WHERE au_id = '409-56-7088'

-- Set a variable to the error value for
-- the DELETE statement.
SELECT @.del_error = @.@.ERROR

-- Execute the INSERT statement.
INSERT authors
VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',
'6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
-- Set a variable to the error value for
-- the INSERT statement.
SELECT @.ins_error = @.@.ERROR

-- Test the error values.
IF @.del_error = 0 AND @.ins_error = 0
BEGIN
-- Success. Commit the transaction.
PRINT "The author information has been replaced"
COMMIT TRAN
END
ELSE
BEGIN
-- An error occurred. Indicate which operation(s) failed
-- and roll back the transaction.
IF @.del_error <> 0
PRINT "An error occurred during execution of the DELETE
statement."

IF @.ins_error <> 0
PRINT "An error occurred during execution of the INSERT
statement."

ROLLBACK TRAN
END
GO

How can I handle an error

Is it possible to catch and error and then keep the process going in a stored procedure?

So if an update encounters a primary key violation on a row, is it possible to skip that row and keep the process going?

You can use a try block in a stored procedure

http://msdn2.microsoft.com/en-us/library/ms175976.aspx

|||

Ken,

I have looked at the try block, but I'm not sure how to set it up. Here is what I'm doing, can you giving me any help on adding the try block so it will keep processing after it catches a primary key violation on a row?

 @.KeepClientIDint, @.RemoveClientIDintASBEGIN UPDATE tbOrgCodesSET tblOrgCodes.Client_ID = @.KeepClientIDWHERE tblOrgCodes.Client_ID = @.RemoveClientIDEND
|||

I added the try/catch and it stopped the error, but it did not continue the process. What do I need to do to get it to continue?

@.KeepClientIDint, @.RemoveClientIDintASBEGINBEGIN TRYUPDATE tbOrgCodesSET tblOrgCodes.Client_ID = @.KeepClientIDWHERE tblOrgCodes.Client_ID = @.RemoveClientIDEND TRYBEGIN CATCH--?END CATCHEND
|||

Hi Jack,

We can catch the exception that is thrown within this single update. If the update affects serveral rows, we cannot only ignore one row and proceed with other rows.

However, if you're calling the DataAdapter.Update method to update rows. When one row throws exception, and you need to go on with other rows, you can set DataAdapter.ContinueUpdateOnError to true, so that it will continue with the next row when error generates.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!