This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Prepared by Bihag Thaker
The stored procedure must ensure its atomicity, i.e. it either executes all statements or none. Client code (caller of a stored procedure) expects it to be this way.
It does not know how many statements are actually executed and may not implement any transaction support. In case of failure, the stored procedure must properly roll back all changes.
Before rolling back a transaction, it should check the value of @@TRANCOUNT
SQL Server 2005 and later versions should use BEGIN TRY…END TRY, BEGIN CATCH…END CATCH syntax for exception handling in stored procedures.
The stored procedure should not hide/suppress errors. In cases when business functionality requires a client code to call several stored procedures/queries in a row and considers the action as an atomic action, client code must be implementing transaction support, which normally relies on the fact that an exception is raised if stored procedure fails.
If stored procedure hides an error, client transaction support will not work.
To follow this standard, throw an exception with RAISERROR() function in BEGIN CATCH…END CATCH block, pass the error details to the application and let the application handle the error. Pass the Error Number with a @ErrorNumber output parameter.
Following should be the general logic for implementing transaction along with proper exception handling:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
BEGIN TRY BEGIN TRANSACTION --DML Statements go here within transaction. COMMIT TRANSACTION RETURN 0 END TRY BEGIN CATCH DECLARE @ErrorNumber INT DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState) RETURN 1 END CATCH |
Leave a Reply