Guide to Using TRY…CATCH to Handle SQL Server Errors

TRY…CATCH to Handle SQL Server Errors,The TRY…CATCH announcement in Transact-SQL detects and handles errors situations in database applications. This declaration is the cornerstone of SQL Server blunders handling and is an important part of growing strong database programs. TRY…CATCH applies to SQL Server starting with 2008, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse.

TRY…CATCH to Handle SQL Server Errors,Introducing TRY…CATCH

TRY…CATCH works by way of allowing you to specify two Transact-SQL statements: one that you want to “try” and some other to apply to “seize” any mistakes that could arise. When SQL Server encounters a TRY…CATCH announcement, it right now executes the announcement covered within the TRY clause. If the TRY announcement executes efficaciously, SQL Server movements on. However, if the TRY assertion generates an blunders, SQL Server executes the CATCH statement to address the mistake gracefully.

The fundamental syntax takes this shape:

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

TRY…CATCH to Handle SQL Server Errors,Example

It’s smooth to understand the use of this announcement via an instance. Imagine that you are the administrator of a human assets database that carries a table named “Employees,” which incorporates records about each of the personnel in your corporation. That desk uses an integer worker ID variety as the number one key. You would possibly try to use the statement underneath to insert a new worker into your database:

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

Under ordinary instances, this declaration would add a row to the Employees table. However, if an employee with ID 12497 already exists in the database, putting the row could violate the primary key constraint and bring about the following mistakes:

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.

TRY…CATCH to Handle SQL Server Errors,While this mistake provides you with the data you need to troubleshoot the problem, there are issues with it. First, the message is cryptic. It consists of errors codes, line numbers and different facts this is incomprehensible to the common person. Second, and more importantly, it causes the statement to abort and could cause an utility crash. The opportunity is to wrap the assertion 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 = '[email protected]',
@body = 'An error occurred creating a new employee record.',
@subject = 'Employee ID Duplication Error' ;
END CATCH

In this case, any errors that occur are suggested to both the consumer executing the command and the [email protected] electronic mail cope with. The mistakes proven to the consumer is:

Error: Violation of PRIMARY KEY constraint 'PK_employee_id'. 
Cannot insert duplicate key in object 'dbo.employees'.
Mail queued.

TRY…CATCH to Handle SQL Server Errors,Application execution maintains typically, permitting the programmer to deal with the error. Use of the TRY…CATCH statement is an fashionable manner to proactively come across and handle errors that occur in SQL Server database packages.

Note: If you need to examine greater about the Structured Query Language, take a look at out Introduction to SQL.