A Step-By-Step Guide to Using TRY/CATCH to Handle SQL Server Errors

Identify errors without interrupting execution

A toy hazard sign surrounded by other various road warning signs

Larry Washburn / Getty Images

The TRY/CATCH statement in Transact-SQL detects and handles error conditions in database applications. This statement is the cornerstone of SQL Server error handling and is an important part of developing robust database applications.

TRY/CATCH applies to SQL Server starting with 2008, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse.

Introducing TRY/CATCH

TRY./CATCH works by specifying two Transact-SQL statements: one that you want to "try" and another to use to "catch" any errors that might arise. When SQL Server encounters a TRY/CATCH statement, it immediately executes the statement included in the TRY clause. If the TRY statement executes successfully, SQL Server moves on. However, if the TRY statement generates an error, SQL Server executes the CATCH statement to handle the error gracefully.

The basic syntax takes this form:

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

TRY/CATCH Example

Consider a human resources database that contains a table named employees, which contains information about each of the employees in a company. That table uses an integer employee ID number as the primary key.

You might attempt to use the statement below to insert a new employee into your database:

INSERT INTO employees(id, first_name, last_name, extension)VALUES(12497, 'Mike', 'Chapple', 4201)

Under normal circumstances, this statement would add a row to the Employees table. However, if an employee with ID 12497 already exists in the database, inserting the row would violate the primary key constraint and result in the following error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_employee_id'. Cannot insert duplicate key in object 'dbo.employees'.
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_employee_id'. Cannot insert duplicate key in object 'dbo.employees'.The statement has been terminated.

The alternative is to wrap the statement in a TRY…CATCH statement, as shown here:

BEGIN TRY
INSERT INTO employees( id, first_name, last_name, extension)
VALUES(12497, 'Mike', 'Chapple', 4201)
END TRY
BEGIN CATCH
PRINT 'ERROR: ' + ERROR_MESSAGE( );
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Employee Mail',
@recipients = 'hr@foo.com',
@body = 'An error occurred creating a new employee record.',
@subject = 'Employee Database Error' ;
END CATCH

In this example, any errors that occur are reported to both the user executing the command and the hr@foo.com e-mail address. The error shown to the user is:

BEGIN TRY

INSERT INTO employees( id, first_name, last_name, extension)
VALUES(12497, 'Mike', 'Chapple', 4201)END TRYBEGIN CATCHPRINT 'ERROR: ' + ERROR_MESSAGE( );EXEC msdb.dbo.sp_send_dbmail@profile_name = 'Employee Mail',@recipients = 'hr@foo.com',@body = 'An error occurred creating a new employee record.',@subject = 'Employee Database Error' ;END CATCH

Application execution continues normally, allowing the programmer to handle the error. Use of the TRY/CATCH statement is an elegant way to proactively detect and handle errors that occur in SQL Server database applications.

Learning More

To learn more about the Structured Query Language, check out our article Fundamentals of SQL.

Format
mla apa chicago
Your Citation
Chapple, Mike. "A Step-By-Step Guide to Using TRY/CATCH to Handle SQL Server Errors." ThoughtCo, Dec. 6, 2021, thoughtco.com/try-catch-for-sql-server-errors-1019840. Chapple, Mike. (2021, December 6). A Step-By-Step Guide to Using TRY/CATCH to Handle SQL Server Errors. Retrieved from https://www.thoughtco.com/try-catch-for-sql-server-errors-1019840 Chapple, Mike. "A Step-By-Step Guide to Using TRY/CATCH to Handle SQL Server Errors." ThoughtCo. https://www.thoughtco.com/try-catch-for-sql-server-errors-1019840 (accessed March 19, 2024).