Error Message handling and Rollback

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error Message handling and Rollback

Author

Topic

Hub
Starting Member

5 Posts

Posted - 2011-02-10 : 23:19:28

I encounter issue when the script below execuite halfway, somehow it stop without throwing any error message and it does now rolled back the data.

Need to knows why it happens (Assuming no Kill Process is execute)
Sample Script below : (The script stop to perform insert statement in Try Block)
Is there any better way to write the script to control the
1.Error Message handling
2.Rollback
TQ

BEGIN TRANSACTION
BEGIN TRY
-- UPDATE STATEMENT 1
-- UPDATE STATEMENT 2
-- INSERT STATEMENT
-- DELETE STATEMENT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

IF @@TRANCOUNT > 0 BEGIN
COMMIT TRANSACTION
END

Sachin.Nand

2937 Posts

Posted - 2011-02-11 : 00:49:53

quote:

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

quote:

IF @@TRANCOUNT > 0 BEGIN
COMMIT TRANSACTION
END

Why are you doing this ?

PBUH

Hub
Starting Member

5 Posts

Posted - 2011-02-17 : 03:26:30

In the transaction there are Try and Catch.
If there is any error encounter within the transaction, it will jump to the catch and rollback
If no error, then commit the transaction.

Actually, this method of writing is found in BOL and I used it in our environment.

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
-- Generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources