Introduction:
In this article,i am going to explain about how to handle the exception in transactions using sql server 2008.

Main:

SQL Server offers major improvements in error handling inside T-SQL transactions. As of SQL Server 2005, you can catch T-SQL and transaction abort errors using the TRY/CATCH model without any loss of the transaction context. The only types of errors that the TRY/CATCH construct can’t handle are those that cause the termination of your session (usually errors with severity 21 and above, such as hardware errors). The syntax is shown here:

BEGIN TRY --sql statements END TRY BEGIN CATCH --sql statements for catching your errors END CATCH

If an error within an explicit transaction occurs inside a TRY block, control is passed to the CATCH block that immediately follows. If no error occurs, the CATCH block is completely skipped.

You can investigate the type of error that was raised and react accordingly. To do so, you can use the ERROR_xxx functions to return error information in the CATCH block, as shown in below,

T-SQL exception handling example

BEGIN TRY SELECT 5/0 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrNumber, ERROR_SEVERITY() AS ErrSeverity, ERROR_STATE() AS ErrState, ERROR_PROCEDURE() AS ErrProc, ERROR_LINE() AS ErrLine, ERROR_MESSAGE() AS ErrMessage END CATCH

You can examine the value reported by any of the various ERROR_xxx functions to decide what to do with the control flow of your procedure and whether to abort any transactions. In our example in below examble, which attempts to divide by zero, here are the values returned by the error functions. (The ERROR_PROCEDURE function returns NULL in this example because the exception did not occur within a stored procedure.)

ErrNumber ErrSeverity ErrState ErrProc ErrLine ErrMessage
———- ———— ———- ——– ——– ———————————-
8134 16 1 NULL 2 Divide by zero error encountered.

When you experience a transaction abort error inside a transaction located in the TRY block, control is passed to the CATCH block. The transaction then enters a failed state in which locks are not released and persisted work is not reversed until you explicitly issue a ROLLBACK statement. You’re not allowed to initiate any activity that requires opening an implicit or explicit transaction until you issue a ROLLBACK.

Certain types of errors are not detected by the TRY/CATCH block, and you end up with an unhandled exception even though the error occurred inside your TRY block. If this happens, the CATCH block is not executed. This is because CATCH blocks are invoked by errors that take place in actual executing code, not by compile or syntax errors. Two examples of such errors are syntax errors and statement-level recompile errors (for example, selecting from a nonexistent table). These errors are not caught at the same execution level as the TRY block, but at the lower level of execution—when you execute dynamic SQL or when you call a stored procedure from the TRY block. For example, if you have a syntax error inside a TRY block, you get a compile error and your CATCH block will not run, as shown here:

-- Syntax error doesn't get caught BEGIN TRY SELECT * * FROM Customer END TRY BEGIN CATCH PRINT 'Error' END CATCH

The result is an error from SQL Server, not from your CATCH block, as follows:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘*’.

Statement-level recompilation errors also don’t get caught by CATCH blocks. For example, using a nonexistent object in a SELECT statement in the TRY block forces an error from SQL Server, but your CATCH block will not execute, as shown here:

-- Statement level recompilation doesn't get caught BEGIN TRY SELECT * FROM NonExistentTable END TRY BEGIN CATCH PRINT 'Error' END CATCH

The result is an error from SQL Server, as follows:

Msg 208, Level 16, State 1, Line 3
Invalid object name ‘NonExistentTable’.

When you use dynamic SQL or a stored procedure, these types of compile errors do get caught because they are part of the current level of execution. Each of the SQL blocks shown in below examble will execute the CATCH block.

Catching syntax and recompilation errors in dynamic SQL and stored procedure calls with exception handlers

-- Dynamic SQL Example BEGIN TRY EXEC sp_executesql 'SELECT * * FROM Customer' END TRY BEGIN CATCH PRINT 'Error' END CATCH GO   -- Stored Procedure Example CREATE PROCEDURE MyErrorProc AS SELECT * FROM NonExistentTable GO   BEGIN TRY EXEC MyErrorProc END TRY BEGIN CATCH PRINT 'Error' END CATCH

Conclusion:
Hope this helps,
Happy Coding.