Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Hong Kong
    Posts
    1

    Post Unanswered: 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?

    KEVIN

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    from 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •