TRY CATCH – Exception Handling in SQL Server
Similar to C++, Java and other languages SQL Server also has a mechanism to handle exceptions by using TRY-CATCH construct. The TRY block contains the SQL statements that may raise an error and CATCH block contains the handling mechanism to process the error. When any error is raised in the TRY block the control is immediately transferred to the CATCH block, where the Error is handled.
–> Following rules should be taken care off while using TRY-CATCH constructs:
– A TRY block must be followed immediately by the CATCH block.
– Both TRY & CATCH blocks must be inside a Batch, Stored Procedure or a Trigger.
– Only Errors with severity between 10 & 20 that do not close the database connection are caught & handled by TRY-CATCH constructs.
– As per MS BOL, Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block. And Errors that have a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.
–> let’s check how to use TRY-CATCH block:
USE [tempdb] GO --// Create a test Stored Procedure CREATE PROC testPrc (@val VARCHAR(10)) AS BEGIN SELECT 1/@val AS operation END GO --// Test for Divide by 0 (Divide by zero error encountered.) BEGIN TRY EXEC testPrc '0' END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINE END CATCH GO --// Test for Datatype conversion (Conversion failed when converting the varchar value 'a' to data type int.) BEGIN TRY EXEC testPrc 'a' END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINE END CATCH GO --// Test nested TRY-CATCH for "Divide by 0" & "Datatype conversion" errors both. BEGIN TRY EXEC testPrc 'a' END TRY BEGIN CATCH SELECT 'outer block', ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINE BEGIN TRY SELECT 1/0 AS operation END TRY BEGIN CATCH SELECT 'inner block', ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINE END CATCH END CATCH GO --// Test for violation of PK Constraint (Violation of PRIMARY KEY constraint 'PK__testTable__2C3393D0'. Cannot insert duplicate key in object 'dbo.testTable'.) BEGIN TRY CREATE TABLE testTable (a INT PRIMARY KEY) INSERT INTO testTable VALUES(1) INSERT INTO testTable VALUES(1) END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINE END CATCH GO SELECT * FROM testTable -- Contains single record with value 1 --// Test for recreating a table that already exists (There is already an object named 'testTable' in the databASe.) BEGIN TRY CREATE TABLE testTable (a INT PRIMARY KEY) END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINE END CATCH GO --// Test for inserting NULL value on Primary Key column (Cannot insert the value NULL into column 'a', table 'tempdb.dbo.testTable'; column does not allow nulls. INSERT fails.) BEGIN TRY INSERT INTO testTable VALUES(NULL) END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ERROR_ID, ERROR_MESSAGE() AS ERROR_MSG, ERROR_SEVERITY() AS ERROR_SEVERITY, ERROR_STATE() AS ERROR_STATE, ERROR_PROCEDURE() AS ERROR_PROCEDURE, ERROR_LINE() AS ERROR_LINE END CATCH GO --// Final Cleanup DROP TABLE testTable DROP PROC testPrc GO
MS BOL Links for TRY-CATCH:
http://msdn.microsoft.com/en-us/library/ms175976.aspx
http://msdn.microsoft.com/en-us/library/ms179296%28v=SQL.90%29.aspx
http://msdn.microsoft.com/en-us/library/ms179495.aspx (Error Information)
Hi,
Thank you for such clear and exhaustive explanations on SQL Concepts. It has been a wonderful learning experience through your blogs.
I am new to this field and I am glad I came across this site.
I had a question one on of the topics:
About rules on TRY CATCH – You had mentioned that the Try-Catch construct should be specified only inside a batch / procedure / trigger. But I was able to execute one of the sample TRY CATCH block of queries mentioned in your blog without placing them inside batch / procedure / trigger.
Could you please let me know what I am missing here.
Thanks in advance.
Hi,
Thank you for such clear and exhaustive explanations on SQL Concepts. It has been a wonderful learning experience through your blogs.
I am new to this field and I am glad I came across this site.
I had a question on one of the topics:
About rules on TRY CATCH – You had mentioned that the Try-Catch construct should be specified only inside a batch / procedure / trigger. But I was able to execute one of the sample TRY CATCH block of queries mentioned in your blog without placing them inside batch / procedure / trigger.
Could you please let me know what I am missing here.
Thanks in advance.
Divya
Divya,
In my statement as you’ve mentiond “Inside batch / procedure / trigger”, here ‘batch” means any collection of SQL queries you are executing, not mandatory to have GO batch separator. SO, this should also work.