Browse Tutorials
 Exception Handling in SQL Server  by TRY…CATCH

Exception Handling in SQL Server by TRY…CATCH

18 Mar 2024
Intermediate
252K Views
9 min read

Exception Handling in SQL Server: An Overview

SQL Server also provides an exception-handling mechanism like any other programming language like C#, C++, Java, C, etc. It has built-in support for constructs like TRY, and CATCH that can handle errors elegantly. In this SQL Server tutorial, we will understand how to handle errors in SQL Server using TRY...CATCH statements.

To move forward you must know what exceptions are and their type in SQL Server. If you aren't aware, refer to SQL Server Exceptions Working.

Read More: Basics of SQL Commands

TRY...CATCH Construct in SQL Server

To handle exceptions in Sql Server we have TRY...CATCH blocks. We need to put the T-SQL statements that could cause an exception in the TRY block and a CATCH block immediately after it. If there is an error in code within the TRY block, the control will automatically jump to the corresponding CATCH blocks. A TRY...CATCH construct cannot span multiple blocks of Transact-SQL statements.

TRY...CATCH Syntax


 BEGIN TRY
--T-SQL statements
--or T-SQL statement blocks
END TRY
BEGIN CATCH
--T-SQL statements
--or T-SQL statement blocks
END CATCH 

Read More: SQL Server Interview Questions and Answers

How does TRY...CATCH helps in Handling Errors and Exceptions.

In the above syntax code,

  1. The exception-causing SQL statements are in the BEGIN TRY block.
  2. If any exception arises, the control is immediately transferred to the BEGIN CATCH block.
  3. In the BEGIN CATCH block, there are statements to be executed if an exception occurs in the BEGIN TRY block. In other words, the statements inside the BEGIN CATCH block are used for handling the program flow in case the exception gets triggered.
  4. If no exception arises i.e. all the SQL statements inside the BEGIN TRY block are executed, the control is not transferred to the BEGIN CATCH block.

Error Functions used within CATCH Block

1. ERROR_NUMBER()

This returns the error number and its value is the same as for @@ERROR function.

2. ERROR_LINE()

This returns the line number of the T-SQL statement that caused an error.

3. ERROR_SEVERITY()

This returns the severity level of the error.

4. ERROR_STATE()

This returns the state number of the error.

5. ERROR_PROCEDURE()

This returns the name of the stored procedure or trigger where the error occurred.

6. ERROR_MESSAGE()

This returns the full text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Exception Handling Example


BEGIN TRY
DECLARE @num INT, @msg varchar(200)
---- Divide by zero to generate Error
SET @num = 5/0
PRINT 'This will not execute'
END TRY
BEGIN CATCH
PRINT 'Error occured that is'
set @msg=(SELECT ERROR_MESSAGE())
print @msg;
END CATCH
GO

Output

 
BEGIN TRY
DECLARE @num INT
---- Divide by zero to generate Error
SET @num = 5/0
PRINT 'This will not execute'
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;
GO

Output

Note

  1. A TRY..CATCH block combination catches all the errors having a severity between 11 and 19.
  2. The CATCH block is executed only if there is an error that occurs in T-SQL statements within the TRY block otherwise the CATCH block is ignored.
  3. Each TRY block is associated with only one CATCH block and vice versa.
  4. TRY and CATCH blocks can’t be separated from the GO statement. We need to put both TRY and CATCH blocks within the same batch.
  5. TRY...CATCH blocks can be used with transactions. We check the number of open transactions by using the @@TRANCOUNT function in SQL Server.
  6. XACT_STATE function within the TRY...CATCH block can be used to check whether an open transaction is committed. It will return -1 if the transaction is not committed else will return 1.

Techniques for testing and debugging TRY...CATCH blocks

  • Deliberate Error Induction: Intentionally induce errors within the TRY block to trigger the CATCH block's execution path.
  • PRINT Statements: Use PRINT statements within the TRY and CATCH blocks to output messages indicating the execution flow.
  • Error Information Views: Examine error information by querying system views such as sys.messages and retrieve specific error details using functions like ERROR_MESSAGE().
  • SQL Server Management Studio (SSMS) Debugging: Utilize SSMS debugging features such as breakpoints, stepping through code, and inspecting variable values to troubleshoot TRY...CATCH blocks effectively.

Best Practices for Implementing TRY...CATCH in SQL Server

  • Use for Error Handling: Apply TRY...CATCH blocks primarily for error handling and gracefully managing exceptions in SQL Server code.
  • Keep Scope Limited: Limit the scope of TRY...CATCH blocks to the smallest possible section of code where errors might occur.
  • Capture Relevant Error Information: Capture relevant error information within the CATCH block using functions like ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_STATE(), ERROR_PROCEDURE(), and ERROR_LINE().
  • Use RAISEERROR() or THROW: Raise custom errors within the CATCH block using the RAISEERROR() or THROW statements to provide informative error messages and control error severity.
  • Handle Specific Errors: Handle specific error conditions separately within the CATCH block.
  • Rollback Transactions: Rollback open transactions within the CATCH block to maintain data consistency and integrity in case of errors.
  • Logging and Alerting: Implement logging mechanisms to record error details in a log table or file for later analysis, and configure alerts to notify administrators of critical errors.
  • Test and Validate: Thoroughly test and validate the error-handling logic by intentionally inducing errors and verifying the behavior of the TRY...CATCH blocks in various scenarios.
  • Keep CATCH Blocks Lightweight: Keep the CATCH blocks lightweight by avoiding complex logic and expensive operations to ensure minimal impact on performance.
Summary

In this article, I have tried to explain Exception handling in SQL Server with examples. I hope after reading this article you will know how to handle exceptions in SQL Server. If you want to gain a practical understanding, you can enroll in our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

FAQs

Q1. How does try-catch handle exceptions in SQL Server?

TRY...CATCH in SQL Server encapsulates error-prone code within a TRY block; upon error, control transfers to the CATCH block for error handling and recovery.

Q2. What are the three types of exceptions in SQL?

  1. User-Defined Exceptions
  2. System Exceptions
  3. RAISERROR Exceptions

Q3. How do you try-catch divide by zero in SQL Server?

BEGIN TRY
    DECLARE @result FLOAT;
    SET @result = 10 / 0; -- Division by zero error
END TRY
BEGIN CATCH
    SELECT 'Error occurred: ' + ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this