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
Showing posts with label onerror. Show all posts
Showing posts with label onerror. Show all posts
Wednesday, March 7, 2012
Subscribe to:
Posts (Atom)