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

No comments:

Post a Comment