How is TRY….CATCH used to fix SQL server errors?

TechnoAdmin

The Transact-SQL TRY….CATCH statement 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 2008 and later, Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse.

Introducing TRY…CATCH

TRY….CATCH works by allowing you to specify two Transact-SQL statements: one that you want to “test” and one that you can use to “catch” any errors that may arise. When SQL Server encounters a TRY….CATCH statement, it immediately executes the statement contained in the TRY clause. If the TRY statement succeeds, SQL Server continues. However, if the TRY statement fails, SQL Server executes the CATCH statement to easily handle the error.

The basic syntax takes this form:

TEST START 
 { sql_statement | statement block } 
 END TEST 
 BEGIN CAPTURE 
 [ { sql_statement | statement_block } ] 
 END CATCH 
 [ ; ]

TRY…CATCH Example

It is easy to understand the use of this statement through an example. Imagine that you are the administrator of a human resources database that contains a table called “Employees” that contains information about each of the employees in your organization. That table uses an integer employee ID number as the primary key. You can try to use the following statement to insert a new employee in your database:

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

Under normal circumstances, this expression would add a line 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 a duplicate key into the ‘dbo.employees’ object.
.The declaration has been cancelled.

Although this error provides you with the information you need to fix the problem, there are two problems with it. First, the message is cryptic. It includes error codes, line numbers, and other information that is incomprehensible to the average user. Second, and more importantly, it causes the statement to break and could cause the application to crash.
The alternative is to wrap the statement in a TRY….CATCH statement, as shown here:

 START TEST 
 INSERT IN employees ( id, firstname, lastname, extension) 
 .VALUES(12497, 'Mike', 'Chapple', 4201) 
 END TEST 
 BEGIN CAPTURE 
 PRINT 'ERROR: ' + ERROR_MESSAGE( ); 
 EXEC msdb.dbo.sp_send_dbmail 
 @profile_name = 
 'Email@recipients = @'[email protected]', 
 @body = @body = 'An error occurred while creating a new employee record', 
 >.Subject = @'Employee ID duplication error' ; 
 END OF CAPTURE

In this example, any errors that occur are reported to both the user running the command and the email address of [email protected] The error displayed to the user is:

Error: Violation of PRIMARY KEY constraint ‘PK_employee_id’.
Cannot insert a duplicate key into the ‘dbo.employees’ object.
.Mail in queue.

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

More information

For more information on Structured Query Language, see Introduction to SQL.




			


	



							

					

					

						
TechnoAdmin

						

						

							More Posts