Introduction
Below is the error handling template that we can use to manage error from SQL server 2005 onwards. Before jumping to template, let us first discuss few terms that will be used later in the tip. To know the basics of SQL server error, please visit my first article SQL server error details.
Background
@@TRANCOUNT
It returns the number of active transactions in the current session. It can be used to find the level of nesting of transaction.
BEGIN TRAN
statement increments the transaction count by 1
.
COMMIT TRAN
statement decrements the transaction count by 1
.
ROLLBACK TRAN
resets the transaction count to 0
.
It cannot be used to check if the current transaction can be commited or not.
SAVE TRANSACTION
It allows a mechanism to rollback a portion of a transaction. It will help us to commit a part of a transaction out of a large batch of script when something fails. It does not affect the @@TRANCOUNT
value. But while using Rollback, we need to specify the Save Point name. If no save point name is specified, it will rollback all transactions. ROLLBACK TRANSACTION savepoint_name
does not decrement @@TRANCOUNT
value.
XACT_STATE
Introduced in SQL server 2005. XACT_STATE()
is a scalar function. It gives us information about the current state of a request. It also says if the transaction is capable of being committed or not. But it cannot be used to determine nested transaction. It returns 3 values:
1
: The current request has an active user transaction.The transaction can be committed. 0
: There is no active transaction.
-1
: The current request has an active user transaction, but some error occurred and transaction cannot be committed.
TRY--CATCH
It is provided with SQL server 2005 to handle error as we do in other programming languages like C# . But here we don't have FINALLY
block.
If any error is raised in TRY
block, control will be passed to CATCH
block.
Error Handling in Template
We can use try catch
to handle error as below:
BEGIN TRY
END TRY
BEGIN CATCH
END CATCH
Below is the complete template that can be used to handle error:
CREATE PROCEDURE [USP_Procedure_Name]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TranCountAtStart INT;
SET @TranCountAtStart = @@TRANCOUNT;
BEGIN TRY
IF @TranCountAtStart = 0
BEGIN TRANSACTION
ELSE
SAVE TRANSACTION USP_Procedure_Name;
IF @TranCountAtStart = 0
COMMIT;
END TRY
BEGIN CATCH
DECLARE @ERRORMESSAGE NVARCHAR(512),
@ERRORSEVERITY INT,
@ERRORNUMBER INT,
@ERRORSTATE INT,
@ERRORPROCEDURE SYSNAME,
@ERRORLINE INT
SELECT
@ERRORMESSAGE = ERROR_MESSAGE(),
@ERRORSEVERITY = ERROR_SEVERITY(),
@ERRORNUMBER = ERROR_NUMBER(),
@ERRORSTATE = ERROR_STATE(),
@ERRORPROCEDURE = ERROR_PROCEDURE(),
@ERRORLINE = ERROR_LINE()
SET @ERRORMESSAGE =
(
SELECT
'Error:' + convert(nvarchar(50),@ErrorNumber) + space(1) +
'Severity:' + convert(nvarchar(50),@ErrorSeverity) + space(1) +
'State:' + convert(nvarchar(50),@ErrorState) + space(1) +
'Routine_Name: ' + isnull(@ErrorProcedure,'') + space(1) +
'Line:' + convert(nvarchar(50),@ErrorLine) + space(1) +
'Message: ' + @ErrorMessage + space(1) +
'ExecutedAs:' + SYSTEM_USER + space(1) +
'Database:' + DB_NAME() + space(1) +
'OSTime:' + convert(nvarchar(25),CURRENT_TIMESTAMP,121)
)
IF @TranCountAtStart = 0
ROLLBACK TRANSACTION
IF @TranCountAtStart > 0
ROLLBACK TRANSACTION USP_Procedure_Name;
RAISERROR (@ERRORMESSAGE,16,1)
END CATCH
END
If you are using SQL server 2012 or above, then you can use the below template:
CREATE PROCEDURE [USP_Procedure_Name]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TranCountAtStart INT;
SET @TranCountAtStart = @@TRANCOUNT;
BEGIN TRY
IF @TranCountAtStart = 0
BEGIN TRANSACTION
ELSE
SAVE TRANSACTION USP_Procedure_Name;
IF @TranCountAtStart = 0
COMMIT;
END TRY
BEGIN CATCH
DECLARE @ERRORMESSAGE NVARCHAR(512),
@ERRORSEVERITY INT,
@ERRORNUMBER INT,
@ERRORSTATE INT,
@ERRORPROCEDURE SYSNAME,
@ERRORLINE INT,
@XASTATE INT
SELECT
@ERRORMESSAGE = ERROR_MESSAGE(),
@ERRORSEVERITY = ERROR_SEVERITY(),
@ERRORNUMBER = ERROR_NUMBER(),
@ERRORSTATE = ERROR_STATE(),
@ERRORPROCEDURE = ERROR_PROCEDURE(),
@ERRORLINE = ERROR_LINE()
SET @ERRORMESSAGE =
(
SELECT
'Error:' + convert(nvarchar(50),@ErrorNumber) + space(1) +
'Severity:' + convert(nvarchar(50),@ErrorSeverity) + space(1) +
'State:' + convert(nvarchar(50),@ErrorState) + space(1) +
'Routine_Name: ' + isnull(@ErrorProcedure,'') + space(1) +
'Line:' + convert(nvarchar(50),@ErrorLine) + space(1) +
'Message: ' + @ErrorMessage + space(1) +
'ExecutedAs:' + SYSTEM_USER + space(1) +
'Database:' + DB_NAME() + space(1) +
'OSTime:' + convert(nvarchar(25),CURRENT_TIMESTAMP,121)
)
SELECT @XASTATE = XACT_STATE();
IF @XASTATE = - 1
ROLLBACK;
IF @XASTATE = 1
AND @TranCountAtStart = 0
ROLLBACK
IF @XASTATE = 1
AND @TranCountAtStart > 0
ROLLBACK TRANSACTION USP_Procedure_Name;
RAISERROR (@ERRORMESSAGE,16,1)
END CATCH
END
These can be used to manage nested transactions as well.
Points of Interest
You can check the correctness and use it in your development. Suggestions and feedback are highly welcomed.
History
- 30th September, 2014: Initial version