We can use try-catch blocks in TSQL since SQL Server 2005.
In the try block;
We write the TSQL code that we want to catch if an error occurs.
In the catch block;
We write the TSQL code to run when an error occurs.
TRY-CATCH USAGE:
|
1 2 3 4 5 6 7 |
BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH { sql_statement | statement_block } END CATCH [ ; ] |
ERROR_NUMBER (): Returns the code of the error.
ERROR_MESSAGE (): Returns a detailed description of the error.
ERROR_SEVERITY (): Returns the severity of the error.
ERROR_STATE (): Returns the status number of the error.
ERROR_LINE (): Returns the line in which the error occurred.
ERROR_PROCEDURE (): Returns the name of the stored procedure or trigger where the error occurred.
In the following example, we created an error and read the information about the error.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
BEGIN TRY SELECT 1/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; |
Likewise, when we run a transaction, we must rollback any operations that are in a possible error. @@TRANCOUNT function returns the number of open transaction.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
BEGIN TRANSACTION; BEGIN TRY -- Consider that a constraint error has occurred. DELETE FROM Customer WHERE CustomerId = 3; 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; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION; |
