Notes from: Erland Sommarskog on ‘Error and Transaction Handling in SQL Server’
XACT_ABORT
SQL Server cannot catch all possible errors like (CLOSED Connection) but it can catch most. So use XACT_ABORTand TRY CATCH together. If the SP does not have a TRY CATCH then only use XACT__ABORT.
SET XACT_ABORT, NOCOUNT ON
Use the above in SPs (15:35 & 24:00) because:
- Catch any errors that cannot be caught by the TRY CATCH block.
- Guarantee that if an error occurs in a nested transaction, it will abort the entire transaction.
- Roll back the transaction when a “Timeout Expired” error occurs.
- In SQL Server (unlike MySQL, Oracle, and Postgre SQL), if an inner stored procedure catches an error, the entire transaction, even from the outer stored procedure, is rolled back.
Savepoints
There is a concept of transaction SAVEPOINTS but ROLLBACK is only possible for some errors and not all (even if you do not use “XACT_ABORT”).
SAVEPOINT is also not supported in distributed transaction. Which means if a SP is called using a Lined Server.
e.g.
BEGIN TRANSACTION MyTran
First part
SAVE TRANSACTION Throw
Second part
ROLLBACK TRANSACTION Throw
Note Throw above is used as the name of the transaction and not the error-handling keyword Throw. This is why it is mandatory to use semi-colon when using the error handling ;THROW key word
Re-raise the error
- Using THROW (RAISEERROR alternative) in the CATCH block start (it with a semi-colon) “;THROW”:
- Use RAISEERROR only if SQL 2005/20008 is used. Or Want to log error with other custom behaiviours.
- THROW is simple to use, original system error is preserved and it ensures execution is aborted.
Example
/*
Author : Usama Ahmed
Created On : 20/09/2023
Description :
History :
*/
CREATE OR ALTER PROCEDURE [dbo].[usp_ActionObject_Template]
@ExistingSessionID [UNIQUEIDENTIFIER] = NULL -- optional if called from another SP
AS
SET XACT_ABORT, NOCOUNT ON
BEGIN
BEGIN TRANSACTION
BEGIN TRY
----- Start Logging ------------------------------------------------------------------------
DECLARE @LocalSessionID [UNIQUEIDENTIFIER] = ISNULL(@ExistingSessionID, NEWID())
, @LocalProcessName [NVARCHAR](128) = OBJECT_NAME(@@PROCID)
EXEC AuditLog.usp_LogProcessExecutionStart @SessionID = @LocalSessionID,
@ProcessName = @LocalProcessName
------------------------------------------------------------------------------------------------
-- Main code for the SP
----- Complete Logging ------------------------------------------------------------------------
DECLARE @LocalTargetRowsUpdated INT = @@ROWCOUNT
DECLARE @LocalSourceRows INT = (SELECT COUNT(1) FROM [dbo].KPIMapping_Lookup)
EXEC AuditLog.usp_LogProcessExecutionEnd @SessionID = @LocalSessionID,
@ProcessName = @LocalProcessName,
@SourceRows = @LocalSourceRows,
@TargetRowsUpdated = @LocalTargetRowsUpdated
-------------------------------------------------------------------------------------------------
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @LocalErrorCode INT = ERROR_NUMBER()
, @LocalErrorProcessName NVARCHAR(128) = ERROR_PROCEDURE()
, @LocalErrorLineNumber INT = ERROR_LINE()
, @LocalErrorDescription NVARCHAR(4000) = ERROR_MESSAGE()
SET @LocalErrorDescription = 'Error Code: ' + LTRIM(STR(@LocalErrorCode)) + ', Error Message: ' + @LocalErrorDescription
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
----- Error Logging ------------------------------------------------------------------------
EXEC AuditLog.usp_LogProcessExecutionError
@SessionID = @LocalSessionID
, @ErrorProcessName = @LocalErrorProcessName
, @ErrorLineNumber = @LocalErrorLineNumber
, @ErrorDescription = @LocalErrorDescription
-------------------------------------------------------------------------------------------------
; THROW
END CATCH
END
GO
Usama's Blog 