Error And Exception Handling in T-SQL

Since it is one of my favorite topics under SQL Server trainings, I always love to talk about Error and Exception handling.

As a good programmer we always say that our software must be error and bug free. But in my knowledge no software in the world is 100% error free or bug free.

Mistakes are bound to happen since we are humans. So, this article is typically for beginners and junior programmers and beginners to T-SQL.

Consider a scenario where you are trying to save some data by using a .Net form that resulted in the following error and you see the following error in the browser.



Now, if I think from an end-user prospective I do not know:

1. What is a table?
2. What is a database?
3. What is an INSERT statement?
4. What is a FOREIGN KEY Constraint?

Because my end-users are not technical people.

What did I do? I was just trying to fill in some details using some form and clicked on the Save/Submit button and things went wrong.

So, instead of this if we show a meaningful error message to the end user that will the better option and guide him about how to avoid this situation again.

So here we need error and exception handling.

Here we are now discussing exception handling in SQL.

Please consider the following example. Now I am intentionally trying to divide 1 by 0 and that is incorrect. So the output is an error.



Now, if I use some exception handling mechanism provided by T-SQL then it is easy for us to handle any exception.

So, in T-SQL we have try and catch blocks for exception handling that is different in syntax from C#.

Here we use the following syntax.

BEGIN TRY
    -- SQL Statements goes here
END TRY
BEGIN CATCH
-- Error handling logic goes here
END CATCH

Now, see the same code with exception handling done to it.

BEGIN TRY
    -- Generate a divide-by-zero error.
    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



By this we have successfully handled this exception and we can show some meaningful error message to the end user.

Benefits of error handling:

1. Show a custom error message to the end users.
2. Separate different types of errors.
3. Stop unnecessary code execution as errors are tough.
4. Enforce domain rules (for example Foreign Key constraint).

Tips:

1. As a good practice I will recommend to have error and exception handling at both languages and database levels.
   
   For example If I am writing some .Net application in C# and my backend is SQL Server then I should have exception handling implemented in both places.
   
   
2. It is always recommended to have exception handling done irrespective of the project, whether it's a small or large and irrespective of the technology.

In my next article I will talk about SQL Transactions, until then stay tuned ... and keep learning ... Cheers!