Hello friends, Today we are going to understand exception handling in SQL Server.
We can handle errors in SQL Server like way in programming languages java, C#, vb, c++ etc. Like these languages SQL Server also allow us two blocks one is TRY and other is CATCH.
The statements or queries we want to execute successfully we kept them into try block. A TRY block must be immediately followed by an associated CATCH block for handle errors and exceptions.
Syntax:
BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH [ { sql_statement | statement_block } ] END CATCH [ ; ]
We can use following system methods to get information about error or exception as follows.
ERROR_NUMBER() returns error number.
ERROR_SEVERITY() returns severity.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger, which caused the error.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_MESSAGE() returns the complete text of the error message.
Below example shows how we can use try catch block to handle exceptions.
// Divide by zero exception
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Go
OUTPUT: ----------- (0 rows affected) ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage ----------- ------------- ----------- ------------------ ----------- ---------------------------------- 8134 16 1 NULL 2 Divide by zero error encountered. (1 row affected)
// Custom exception
BEGIN TRY
RAISERROR('Custom Exception',14,3)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Go
OUTPUT: ----------- ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage ----------- ------------- ----------- ------------------ ----------- ------------------ 50000 14 3 NULL 2 Custom Exception (1 row affected)
TRY CATCH Block with TRANSACTION:
Lets create two tables for Country and States as follows.
CREATE TABLE dbo.Country(CountryId int Primary Key, CountryName varchar(200))
Go
CREATE TABLE dbo.States(StateId int primary key, CountryId int, StateName varchar(200))
Go
Following code try to insert insert record(s) in both tables with in TRANSACTION unit and raising error and handle exceptions.
BEGIN TRAN
BEGIN TRY
INSERT INTO dbo.Country values(1,'INDIA')
RAISERROR('Failed to create country',16,1)
INSERT INTO dbo.States values(1,1,'PUNJAB')
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK;
END CATCH
Go
Explanation: First Query insert data into country table. after that a custom exception raises. Due to this exception control goes to catch block and ROLLBACK all previous executed queries.
If not any error raises (i.e. if we comment RAISERROR Statement) Both queries executed successfully with commit statement of transaction block.
If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.
Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.