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 @@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 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
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.
BEGIN TRY
SELECT GETDATE()
SELECT 1/0--Evergreen divide by zero example!
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 @@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!
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 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.
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