July 9, 2010 at 2:47 am
What kind of errors can ROLLBACK TRANSACTION raise? Are these errors typical of something catastrophic that is (or at least should be) well beyond the scope of reporting back to the end user in an application? I'm asking because I would like to know which architecture of code below is more robust:
1)
BEGIN TRANSACTION
BEGIN TRY
-- Stuff
COMMIT TRANSACTION
END TRY
BEGIN CATCH
EXEC [dbo].[dba_Error_Handler] @isDynamicSQL = 0
ROLLBACK TRANSACTION
END CATCH
or
2)
BEGIN TRANSACTION
BEGIN TRY
-- Stuff
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
EXEC [dbo].[dba_Error_Handler] @isDynamicSQL = 0
END CATCH
This code will be run on both SQL Server 2005 and SQL Server 2008 installations.
My implementation of dba_Error_Handler can be found here. Basically it handles some logic and then reraises the error.
Thank you.
July 9, 2010 at 6:05 am
IMHO, the ROLLBACK statement should be the first instruction in the CATCH block, because the RAISERROR in the error handling procedure would prevent any subsequent statement from executing, if called from an external TRY/CATCH block.
Example:
CREATE PROCEDURE testError
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
-- Stuff
COMMIT TRANSACTION
END TRY
BEGIN CATCH
EXEC [dbo].[dba_Error_Handler] @isDynamicSQL = 0
ROLLBACK TRANSACTION
END CATCH
END
CREATE PROCEDURE testError2
AS
BEGIN
BEGIN TRY
EXEC testError
END TRY
BEGIN CATCH
PRINT 'Error occurred'
PRINT @@Trancount
END CATCH
END
I would also check if a transaction is already in place with @@trancount and either nest a new one (SAVE TRANSACTION) or prevent a new one from starting.
This is my general template for TRY/CATCH and transaction handling in procedures:
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <ProcedureName, sysname, >
AS
BEGIN
DECLARE @localTran bit
IF @@TRANCOUNT = 0
BEGIN
SET @localTran = 1
BEGIN TRANSACTION LocalTran
END
BEGIN TRY
--Insert code here
IF @localTran = 1 AND @@TRANCOUNT > 0
COMMIT TRAN LocalTran
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
IF @localTran = 1 AND @@TRANCOUNT > 0
ROLLBACK TRAN
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
END
Hope this helps
Gianluca
[/code]
-- Gianluca Sartori
July 9, 2010 at 6:15 am
Thanks Gianluca. I was also thinking that the ROLLBACK TRANSACTION should occur first.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply