In SQL Server Management Studio (SSMS), “try” and “catch” are used as part of error handling within Transact-SQL (T-SQL) code. They are used to handle exceptions or errors that may occur during the execution of a batch of SQL statements. Here’s how they work:

  1. TRY: The TRY block contains the code that you want to attempt to execute. If an error occurs within the TRY block, it will trigger an exception.
  2. CATCH: The CATCH block contains the code that handles the exception generated in the TRY block. It allows you to take specific actions in response to the error, such as logging the error message, rolling back transactions, or gracefully terminating the script.

Here is a basic example of how TRY and CATCH blocks are used in T-SQL:

 

Nested TRY CATCH constructs

You can nest TRY CATCH construct inside another TRY CATCH construct. However, either a TRY block or a CATCH block can contain a nested TRY CATCH, for example:

 

 

Using TRY and CATCH blocks is a good practice for handling errors in T-SQL because it allows you to gracefully handle unexpected issues that might arise during the execution of your SQL code. This can be especially important in production environments where you want to minimize the impact of errors on your database operations.

The CATCH block functions

Inside the CATCH block, you can use the following functions to get the detailed information on the error that occurred:

  • ERROR_LINE() returns the line number on which the exception occurred.
  • ERROR_MESSAGE() returns the complete text of the generated error message.
  • ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
  • ERROR_NUMBER() returns the number of the error that occurred.
  • ERROR_SEVERITY() returns the severity level of the error that occurred.
  • ERROR_STATE() returns the state number of the error that occurred.

Note that you only use these functions in the CATCH block. If you use them outside of the CATCH block, all of these functions will return NULL.

 

 

  1. We start by defining a transaction (BEGIN TRANSACTION) at the beginning of our code. Transactions are used to group a set of SQL statements into a single unit of work that can be either committed or rolled back as a whole.
  2. Inside the TRY block, we place the T-SQL statements that we want to execute. In this example, we intentionally perform a division by zero operation to trigger an error.
  3. If an error occurs within the TRY block, the control is immediately transferred to the CATCH block. In the CATCH block, we can handle the error, log it, and optionally roll back the transaction.
  4. Inside the CATCH block, we use various error-related functions (e.g., ERROR_MESSAGE(), ERROR_NUMBER()) to retrieve information about the error and handle it accordingly. In this example, we log the error message.
  5. Depending on your requirements, you can either log the error, re-throw it using the THROW statement, or take any other necessary actions.
  6. The transaction is rolled back within the CATCH block in case of an error. You can also choose to commit it if desired.
  7. Finally, at the end of the code, you can optionally commit the transaction if it hasn’t already been rolled back in the CATCH block.

The built-in support for exception handling in SQL Server enables you to implement error handling code with ease. You can take advantage of this built-in exception handling mechanism in SQL Server to rollback transactions when an error has occurred or use it in DML statements (INSERT, UPDATE and DELETE) to check for errors and handle it gracefully. You can even log errors that are caught inside the CATCH block – I’ll discuss on it and a few advanced features in a future article here.

Click_here_knowmore…..

Leave a Reply

Your email address will not be published. Required fields are marked *