Using TRY-CATCH to Rollback a Transaction
The release of Sql Server 2005 has provided us somany features over its predecessor. No doubt that more preference is given to the tasks performed by the administrator. But there are some new development features added to make your Sql Code more powerful and error resistance, specially; Stored Procedures.
The Feature - “TRY...CATCH”
The Most impressive functionality improvement added for developers is “Exceptional Handling” technique. There is no beneficial reason if you are not writing your code in “Try.. Catch” block.
-
A TRY Block - the TRY block contains the code / script that might cause an exception
-
A CATCH Block - if an exception occurs from one of the statements in the TRY block, control is branched to the CATCH block, where the exception can be handled, logged, and so on.
Checking @@ERROR - the “sql 2000” Way of Handling Errors in Stored Procedure
Just have a look at below Store Procedure example.
CREATE PROC usp_AccountTransaction @AccountNum INT, @Amount DECIMAL AS BEGIN BEGIN TRANSACTION --beginning a transaction.. UPDATE MyChecking SET Amount = Amount - @Amount WHERE AccountNum = @AccountNum IF @@ERROR != 0 --check @@ERROR variable after each DML statements.. BEGIN ROLLBACK TRANSACTION --RollBack Transaction if Error.. RETURN END ELSE BEGIN UPDATE MySavings SET Amount = Amount + @Amount WHERE AccountNum = @AccountNum IF @@ERROR != 0 --check @@ERROR variable after each DML statements.. BEGIN ROLLBACK TRANSACTION --RollBack Transaction if Error.. RETURN END ELSE BEGIN COMMIT TRANSACTION --finally, Commit the transaction if Success.. RETURN END END END GO Yes!.. This is what we used to code a Stored Procedure in Sql 2000; Check for @@ERROR after every DML (Data Manipulation) Statements and Commit / RollBack the transaction. While working with SQL Server 2000, detecting errors could only be handled by checking a global error variable, @@ERROR. Because the The TRY...CATCH block in SQL Server 2005 offers a much more readable syntax and one that developers are more familiar with. And yes, SQL Server 2005 still supports to @@ERROR Approach. In this article we'll look at the new TRY...CATCH block and examine how it can be used to rollback a transaction in the face of an error. Lets move on to it! Handling Errors With SQL Server 2005's TRY...CATCH Blocks In Fact, there is really nothing new to be describe and discuss on TRY...CATCH Block; as we all know with any programming languages, TRY...CATCH block executes a number of statements in the TRY block. If there are no errors in any of the statements, control proceeds to after the CATCH block. If, however, one of the statements causes an error, control branches immediately to the start of the CATCH block. Basic Syntax is, BEGIN TRY Try Statement 1 Try Statement 2 ... Try Statement M END TRY BEGIN CATCH Catch Statement 1 Catch Statement 2 ... Catch Statement N END CATCH BEGIN TRY SELECT GETDATE() SELECT 1/0--Evergreen divide by zero example! END TRY BEGIN CATCH SELECT 'There was an error! ' + ERROR_MESSAGE() RETURN END CATCH; Using TRY...CATCH to Rollback a Transaction in the Face of an Error As you saw in earlier example, one of the downsides of the ALTER PROC usp_AccountTransaction @AccountNum INT, @Amount DECIMAL AS BEGIN BEGIN TRY --Start the Try Block.. BEGIN TRANSACTION -- Start the transaction.. UPDATE MyChecking SET Amount = Amount - @Amount WHERE AccountNum = @AccountNum UPDATE MySavings SET Amount = Amount + @Amount WHERE AccountNum = @AccountNum COMMIT TRAN -- Transaction Success! END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN --RollBack in case of Error -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1) END CATCH END GO Just look at the simplicity and line of code than previous example! In the TRY block a transaction is started and the two Also, you can “re-raises” the error (using RAISERROR) so that the error information will be passed up to your .Net application from where you are calling the Stored Procedure, in case if you want to use the error information to process further steps anyhow. Thats it. lets Code Better! Referenced Links Other Functions / Statements Reffered @@ERROR - Returns the error number for the last Transact-SQL statement executed. Returns 0 if the previous Transact-SQL statement encountered no errors. @@ERROR is cleared and reset on each statement executed RAISEERROR() - Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct. @@TRANCOUNT - The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.s
@@ERROR variable value is reset after each SQL statement, this leads to rather bloated stored procedures, as the variable must be checked after each statement with code to handle any problems.
The following system functions are available in the CATCH block and can be used to determine additional error information:
Function Description
ERROR_NUMBER() Returns the number of the error.
ERROR_SEVERITY() Returns the severity.
ERROR_STATE() Returns the error state number.
ERROR_PROCEDURE() Returns the name of the stored procedure where the error occurred.
ERROR_LINE() Returns the line number inside the routine that caused the error.
ERROR_MESSAGE() Returns the complete text of the error message.
Take a look at below example,
@@ERROR variable approach is that to implement Transaction; we must check this variable after each and every DML SQL statement to determine if an error occurred and, if so, to rollback the transaction. With SQL Server 2005's TRY...CATCH block, however, these types of scripts are greatly simplified.
Lets Alter the Previous Example!
UPDATE statements are performed. If both UPDATEs succeed, the COMMIT will be reached and the transaction committed. If, however, either one produces an error, control will be execute CATCH block where the transaction will be rolled back.
No comments:
Post a Comment