Recently I’ve posted about the topic Handling Error in SQL, in this link, I’ve explained, what is the simplest way to handle the error? This can be implemented in all version of SQL Server.
In SQL Server 2005 offers major improvements in error handling inside T-SQL transactions. Now you can use Try… Catch Block in SQL Transactions Which is available in Programming languages Like C#, VB.NET..,
You can now catch -SQL and transaction abort errors using the TRY/CATCH model without any loss of the transaction context.
The syntax is shown here:
BEGIN TRY
sql statement
END TRY
BEGIN CATCH TRAN_ABORT
--sql statement for catching your errors
END CATCH
This will be functioning the same way of Try… Catch in Programming language.
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 new ERROR functions to return error information in the CATCH block, as shown here:
BEGIN TRY
SELECT 5/0
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
You can examine the value of the ERROR functions to decide what to do with the control flow of your procedure and whether to abort any transactions. 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
Sometimes 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 executionwhen 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:
-- Syntax error doesn't get caught
BEGIN TRY
SELECT * * FROM XYX
END TRY
BEGIN CATCH
PRINT 'Error'
END CATCH
GO
The result is an error from SQL Server, not from your CATCH block:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '*'.
Statement-level recompilation errors don't get caught by CATCH blocks, either. 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:
-- Statement level recompilation doesn't get caught
-- Example - nonexistent object
BEGIN TRY
SELECT * FROM XYX
END TRY
BEGIN CATCH
PRINT 'Error'
END CATCH
GO
The result is an error from SQL Server:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'XYX'.
