Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Error Handling in SQL Server using TRY…CATCH – Querychat

SQL Server provides the ability to handle errors by using the TRY…CATCH block. This mechanism can help us catch the errors in our Transact-SQL (T-SQL) code during execution. We can then change the flow of our code in response to error conditions.

Syntax

BEGIN TRY  
    { sql_statement | statement_block }  
END TRY  
BEGIN CATCH  
    [ { sql_statement | statement_block } ]  
END CATCH  
[ ; ]

Please note that the SQL Server TRY…CATCH block does not support FINALLY as usually found in other programming languages.

Errors caught by the TRY…CATCH block

The TRY…CATCH block in SQL Server catches errors having severity higher than 10 and those that do not close the database connection.

The following conditions are not handled/captured in the CATCH block:

  • Warnings or informational messages
  • Errors with the severity of 20 or higher and stop the database engine task. If an error has a severity of 20 or higher but does not disrupt the database connection, TRY…CATCH handles the error
  • Attention events, such as broken connections
  • Sessions ended by a system administrator by using the KILL statement

There are also types of errors with severity below 20 but not handled by a CATCH block, such as:

  • Compile errors, such as syntax error
  • Object name resolution errors, such as deleting a non-existing table from a database

Functions to obtain error information in a CATCH block

There are system functions that we can use from anywhere within a CATCH block to get the information on the error. If we call these functions outside the scope of the CATCH block, they return NULL.

Here’s the list of the functions:

  • ERROR_NUMBER(), which returns the number of the error
  • ERROR_SEVERITY(), which returns the severity of the error
  • ERROR_STATE(), which returns the error state number
  • ERROR_PROCEDURE(), which returns the name of the stored procedure or trigger where the error happened
  • ERROR_LINE(), which returns the line number that caused the error
  • ERROR_MESSAGE(), which returns the error message text

Example #1 of TRY…CATCH in SQL statements

The following code is a simple example showing us how the TRY…CATCH block works and how we can see the output of the error messages.

BEGIN TRY  
   SELECT CAST('SQL' AS INT) [INT]  
END TRY  
BEGIN CATCH  
   SELECT ERROR_NUMBER() AS ErrNum
 , ERROR_SEVERITY() AS ErrSeverity  
       , ERROR_STATE() AS ErrState  
       , ERROR_PROCEDURE() AS ErrProcedure  
       , ERROR_LINE() AS ErrLine  
       , ERROR_MESSAGE() AS ErrMsg;  
END CATCH;  
GO

In the above code, we’re going to convert the string ‘SQL’ to an integer value. The code generates an exception because the string ‘SQL’ cannot be converted into an integer value. The SELECT statement within the CATCH block executes and shows us the information related to the error, such as the error number, severity, state, and so on.

Let’s see what the results look like when we attempt to execute the code.

Example of SQL Server TRY...CATCH

Example of SQL Server TRY…CATCH

The first result with column name INT would have been the result of what we attempted to do in this select statement: SELECT CAST(‘SQL’ AS INT) [INT]. As we can see, there’s no output here because the code generated an error.

The second result gave us information about the error, such as:

  • The error number is 245
  • The error severity is 16
  • The error occurred at line 2
  • The error state is 1
  • The error procedure is NULL, which indicates that the error was not occurring within a stored procedure
  • The error message is: Conversion failed when converting the varchar value ‘SQL’ to data type int.

Example #2 of TRY…CATCH with successful flow

The example below shows that when there is no error in SQL statements, the code inside the CATCH block does not execute.

BEGIN TRY 
DECLARE @Total AS INT 
   SET @Total = (SELECT CAST('20000' AS INT) + 30000 [INT])

SELECT 'This is the TRY block' AS Result
END TRY  
BEGIN CATCH  
   SELECT 'This is the CATCH block. An error has occurred' AS Result
END CATCH;  
GO

Result:

Result of SQL Server TRY...CATCH

As the SELECT statement inside the TRY block executed, this means that there was no error in the SQL statements inside the TRY block.

Example #3 An error that passes out of the TRY…CATCH block

Following example shows an error that did not flow to the CATCH block.

BEGIN TRY 
SELECT GETDATES()
END TRY  
BEGIN CATCH  
   SELECT ERROR_NUMBER() AS ErrNumber
END CATCH;  
GO

Result:

Msg 195, Level 15, State 10, Line 2

‘GETDATES’ is not a recognized built-in function name.

The error was that there is no built-in function named GETDATES(). The severity level is under 20, but the error did not go through the CATCH block since it was as an object name resolution error.

Example #4 of TRY…CATCH in a Stored Procedure

Let’s see the stored procedure below that uses an error handling technique.

CREATE PROCEDURE sp_ArithmeticDivision 
@Number AS INT,
@Divider AS INT
AS
BEGIN TRY
PRINT @Number / @Divider
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134 
PRINT 'You cannot divide a number with zero.' 
ELSE PRINT 
'Unknown error occurred. Check your inputs and try again.'
END CATCH
GO

It’s important to anticipate common errors that might be going to happen. Handling those and creating programmatic responses can create a better experience and help other users when they’re doing troubleshooting steps.

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc