When SQL statements are being executed, errors may occur. The approach of handling them in SQL Server can be done with TRY-CATCH exception handling. It makes code debugging easier for developers because it enables them to locate the root of any mistakes that may have occurred. We'll demonstrate it in this post and also understand its other aspects.
An exception is an error or problem that occurs during the execution of a Transact-SQL statement or batch. When an exception occurs, SQL Server raises an error message that includes information about the error such as the error number, severity, state, and message text.
Some common examples of exceptions in SQL Server include:
Constraint violation errors (e.g. foreign key violation, unique constraint violation)
Data type conversion errors
Divide-by-zero errors
Permission errors
Object not found errors
Deadlock errors
To handle exceptions in SQL Server, we have to use the TRY-CATCH construct. The TRY block contains the code that might raise an exception, and the CATCH block contains the code that handles the exception. By using TRY-CATCH, you can gracefully handle errors and prevent them from terminating your application. In addition to TRY-CATCH, SQL Server also provides other error-handling mechanisms such as RAISERROR and THROW. RAISERROR is used to generate a custom error message and THROW is used to re-throw an exception that has been caught in a TRY block. Handling exceptions is an important part of developing stable and reliable SQL Server applications. Note that the try-catch mechanism won't work in UDF(User Defined Functions).
Understanding the Try Catch block:
The exception handling of the SQL Try-Catch block is the same as the try-catch block in other programming languages. When the SQL statements are executed in the try block, if any exception arises, then the control is immediately transferred to the catch block which handles the flow of the program in case of exceptions. In catch, we write statements or call procedures to properly handle the exceptions that occurred in the try block as per our requirement. We can also take advantage of system functions, to get detailed information about the error.
ERROR_MESSAGE() - you can take advantage of this function to get the complete error message.
ERROR_LINE() - this function can be used to get the line number on which the error occurred.
ERROR_NUMBER() - this function can be used to get the error number of the error. ERROR_SEVERITY() - this function can be used to get the severity level of the error. ERROR_STATE() - this function can be used to get the state number of the error. ERROR_PROCEDURE() - this function can be used to know the name of the stored procedure or trigger that has caused the error.
Syntax:
Below is the syntax for the TRY CATCH.
BEGIN TRY
END TRY
BEGIN CATCH
END CATCH
Below is the syntax for the Nested TRY CATCH
BEGIN TRY
END TRY
BEGIN CATCH
BEGIN TRY
END TRY
BEGIN CATCH
END CATCH
END CATCH
For example and the demonstration, we create a table and populate it with data. let's have a simple employee table having column Name, Age, Salary, and Department. Use the below statement to create a table and populate it with data.
create table employee(
id int identity(1,1),
name varchar(150),
age tinyint,
salary decimal(10,2),
department varchar(50)
);
set identity_insert employee on;
insert into employee (id, name, age, salary, department) values (1, 'Brittany Jeynes', 34, 75348.30, 'Support');
insert into employee (id, name, age, salary, department) values (2, 'Rita Grahamslaw', 31, 48472.11, 'Marketing');
insert into employee (id, name, age, salary, department) values (3, 'Eloise Stovin', 42, 92411.91, 'Research and Development');
insert into employee (id, name, age, salary, department) values (4, 'Angeline Blaszczyk', 30, 71372.13, 'Research and Development');
insert into employee (id, name, age, salary, department) values (5, 'Kata Yakobowitz', 42, 86797.11, 'Business Development');
insert into employee (id, name, age, salary, department) values (6, 'Minnnie Marling', 21, 101031.2, 'Human Resources');
insert into employee (id, name, age, salary, department) values (7, 'Brnaba Fenney', 42, 98107.23, 'Product Management');
insert into employee (id, name, age, salary, department) values (8, 'Verina Dyson', 42, 83881.92, 'Support');
insert into employee (id, name, age, salary, department) values (9, 'Timothee Pelz', 28, 117771.98, 'Accounting');
insert into employee (id, name, age, salary, department) values (10, 'Terrance Fomichkin', 30, 79487.77, 'Sales');
insert into employee (id, name, age, salary, department) values (11, 'Sanderson Olpin', 47, 137815.37, 'Training');
insert into employee (id, name, age, salary, department) values (12, 'Leona Melsom', 31, 142223.11, 'Engineering');
insert into employee (id, name, age, salary, department) values (13, 'Ingrid Lys', 42, 87863.52, 'Research and Development');
insert into employee (id, name, age, salary, department) values (14, 'Sophia Dungate', 25, 114351.43, 'Accounting');
insert into employee (id, name, age, salary, department) values (15, 'Estrella Kille', 31, 45259.00, 'Legal');
insert into employee (id, name, age, salary, department) values (16, 'Jonah Collecott', 28, 70264.52, 'Accounting');
insert into employee (id, name, age, salary, department) values (17, 'Urbain Squirrel', 21, 53957.32, 'Product Management');
insert into employee (id, name, age, salary, department) values (18, 'Karin Castellani', 31, 89995.29, 'Product Management');
insert into employee (id, name, age, salary, department) values (19, 'Bethena Armin', 28, 41844.10, 'Human Resources');
insert into employee (id, name, age, salary, department) values (20, 'Zonnya Trevna', 28, 56519.36, 'Training');
set identity_insert employee off;
SQL TRY CATCH Example:
For a simple try-catch example, let's try to insert a row in the employee table we created with an age column value out of the range of the tinyint data type and see how the SQL Server handles the exception.
BEGIN TRY
insert into employee(name, age, salary, department) values ('Sana Giggs', 256, 40999, 'Sales');
END TRY
BEGIN CATCH
SELECT
ERROR_MESSAGE() AS [Error Message]
,ERROR_LINE() AS ErrorLine
,ERROR_NUMBER() AS [Error Number]
,ERROR_SEVERITY() AS [Error Severity]
,ERROR_STATE() AS [Error State] ;
END CATCH
As guessed correctly, we naturally will get the error while trying to insert the above statement, as the upper bound of the tinyint data type is 255 and we are trying to insert 256 in the age column, so we received an "Arithmetic overflow error for data type tinyint, value = 256." error.
SQL TRY CATCH Example in Transaction:
Similar, to the above example, we can TRY CATCH with transactions also. Let's modify the above example and include transactions in it.
BEGIN TRANSACTION;
BEGIN TRY
update employee set age = 256 where id = 1;
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;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
With the execution of the above script we can see that, we received the same error. The only reason it was demonstrated, was to show an example of a transaction.
Using TRY CATCH in procedures:
To Handle exceptions in procedures lets use the below script and execute the procedure, This example is basically for showing the syntax of using try-catch in the procedure.
CREATE OR ALTER PROCEDURE [dbo].[usp_update_employee_age]
(@input_age int, @person_id int)
AS
BEGIN
BEGIN TRY
declare @message varchar(500);
BEGIN TRANSACTION;
update employee set age = @input_age where id = @person_id;
SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Transaction Committed'
print @message;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Transaction rolledback';
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE () AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_MESSAGE() AS ErrorMessage;
print @message;
THROW;
END CATCH
END
After creating the above procedure, we can call and execute the procedure with the below statement
EXEC usp_update_employee_age 200, 1;
It will return an error with the same exception, this is a way to use try catch the transaction in the procedure. Change the value within the range of the tinyint data type and execute, it will work without any error. Now, the last important usage of try-catch which must be covered is a custom exception in SQL procedure.
Custom Exception in procedure:
Similar to custom exceptions in java and other languages, we can also have our custom exception handling inside the procedure. There are many scenarios where we need to handle certain cases and if satisfied, we need to send a custom JSON output message with an error or else a custom message with success. We can use RAISERROR or THROW for sending these custom exceptions. Note that both THROW and RAISERROR statements can be used to generate custom errors and re-throw exceptions. However, the THROW statement can re-throw the original exception that has been caught in the CATCH block and RAISERROR statement re-throws an altogether new exception and the original exception is lost. Check the script below. I am adding an extra part, wherein I validate the @input_age variable value first and raise an exception myself based on the requirement. I doing this for the demonstration and to show the syntax only, since nothing another example is coming to my mind now.
CREATE OR ALTER PROCEDURE [dbo].[usp_update_employee_age]
(@input_age int, @person_id int, @output_json nvarchar(max) out)
AS
BEGIN
BEGIN TRY
declare @message varchar(500);
BEGIN TRANSACTION;
if (@input_age > 80) or (@input_age < 18)
begin
select @output_json = N'{
"status" : 0,
"description" : "age provided for the update is not within permitted range",
"data": {}
}';
raiserror(@output_json, 16, 1);
ROLLBACK TRANSACTION;
end
else
begin
update employee set age = @input_age where id = @person_id;
SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Transaction Committed'
print @message;
set @output_json = N'{
"status" : 1,
"description" : "Employee age has been updated",
"data": {}
}';
RAISERROR(@output_json, 0, 1) WITH NOWAIT;
COMMIT TRANSACTION;
end
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
SELECT @message = CONVERT(VARCHAR(30), GETDATE(), 121) + ': Transaction rolledback';
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE () AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_MESSAGE() AS ErrorMessage;
RAISERROR(@message, 0, 1) WITH NOWAIT;
THROW;
END CATCH
END
After creating the above procedure, we can call and execute the procedure with the below statement
declare @output_json nvarchar(max);
EXEC usp_update_employee_age 105, 1, @output_json;
After executing the procedure, we can see the returning variable returning the response as per the case and inputs provided. The above script demonstrates the usage of custom user exceptions, and I prefer to do it this way in many cases.
The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY...CATCH construct:
Compile errors, such as syntax errors, prevent a batch from running.
Errors that occur during statement-level recompilation, such as object name resolution errors occur after compilation because of deferred name resolution.
Object name resolution errors
If any points are missing then you can add them in a comment below.
A few Things to note again are,
We can use the THROW function, without any parameters, but not RAISERROR.
Error Number for THROW function should be greater than or equal to 50000;
Error Severity level should be above 10 and below 19 in RAISERROR to trigger the exception and jump to catch block.
As may be mentioned earlier, TRY CATCH cant be used in a user-defined function.