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.
Take a look at below example,
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 @@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