I currently have an audit process that is part of all procedures and it is very basic at a high level.

• Procedure is executed.
• It inserts into the Audit table with a start time and status of Running.
• It completes with out errors and then updates that record to show a status of Complete.
• If there is an actual error it will update with a status of Error.

However if a user manually aborts the procedures via the SQL Server Management Studio no update will occur. The record will remain as "Running".

Is there any functionality I can use to capture this scenario so I can tie out the audit record?

CodePudding user response：

You simply need to enclose it inside a transaction, and use XACT_ABORT for automatic rollback.

CREATE OR ALTER PROCEDURE YourProc
  -- parameters here
AS

SET NOCOUNT, XACT_ABORT ON;

BEGIN TRAN;

-- everything else here

COMMIT;

Always use SET XACT_ABORT ON if you have a transaction. This ensures that if the code is aborted or it errors, the transaction will be rolled back and not left hanging.

Do not use TRY CATCH unless you want to actually handle the error and log it. It is not necessary to use CATCH to ensure a rollback if you have XACT_ABORT. If you do catch, then rethrow the error using THROW; rather than RAISERROR. For example:

BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK;

    INSERT Audit... ;

    THROW;
END CATCH;

But note that certain errors cannot be caught, and a user abort cannot be caught either. I suggest you use XEvents for that .