MS SQL Server

Try and Catch Blocks in SQL Server

No matter how perfect your code tries to be, there is always a chance of errors. It is, therefore, best to implement error-handling measures to catch and deal with errors before they break the functionality of your applications.

In this guide, you will learn how to implement error handling in SQL Server by using the try and catch blocks.

If you are looking to learn how to generate user-defined error messages, check our tutorial on the SQL Server RAISERROR tutorial.

Introduction

The try and catch blocks in the SQL Server allow you to predict and graciously handle errors that may occur in the SQL Queries.

First, enclose the SQL queries that may cause errors inside a try block. You can then add a catch block once an exception is raised.

The syntax for a try block is as shown:

begin try

-- try to run these statements

end try;

After the try block, you can set a catch block that follows similar syntax as shown:

begin catch

  -- catch statements

end catch

First, the SQL Server will try to run the code inside the try block. If no error/exception occurs, it skips the catch block and proceeds with the execution.

However, if an error occurs inside the try block, the execution jumps inside the catch and executes the code inside that block.

The syntax for a full try/catch block is as shown:

begin try
    -- tryto run me
endtry
begin catch
    -- run meiferrorintry
endcatch;

You can implement measures to handle the raised error inside the catch block, such as displaying messages using raiserror and print statements. Remember, the error message inside the catch block is not returned to the application unless using mechanisms, such as a select statement.

You can use special functions to get detailed information about the error:

  1. ERROR_NUMBER() – returns an internal message-id for the error.
  2. ERROR_SEVERITY() – shows the severity level of the error. A value between 0 and 25 with higher severity indicates a high level of severity. Note a severity value of 20 – 25 is fatal.
  3. ERROR_STATE – State of the error message. A value between 0 – 255.
  4. ERROR_MESSAGE – a descriptive message of the error that has occurred.
  5. ERROR_PROCEDURE() – shows the name of the function, trigger, or stored procedure where the error was raised.

Ensure to use the previous functions inside a catch block; otherwise, they will return a null value.

SQL Server Try/Catch Examples

Let us use a divide by zero error to illustrate how to use the try/catch block. Start by creating a procedure as shown in the following queries:

-- get error information
createprocedureget_errorInfo
as
    selectERROR_NUMBER() aserrorNumber,
        ERROR_SEVERITY() asseverity,
        ERROR_STATE() aserrorState,
        ERROR_PROCEDURE() aserrorProcedure,
        ERROR_MESSAGE() aserrrorMessage,
        ERROR_LINE() aserrorLine

begintry
    -- dividebyzeroerror
    select 1/0;
endtry
begin catch
    -- run proceduretogeterrorinfo
    execget_errorInfo;
end catch;

In the above example, we create a stored procedure to retrieve information about an error. Then, we raise an error by diving by zero.

If we run the query above, we should get an output as:

What happens if there is no error in the catch block as shown:

begintry
    -- no error
    select100/5asresult;
endtry
begin catch
    -- run proceduretogeterrorinfo
    execget_errorInfo;
endcatch;

Since there is no error in the try block, the SQL Server skips the catch blocks and returns the result. An example result is as shown:

Conclusion

This guide covered implementing and using error handling in the SQL Server using the try/catch blocks. In addition, special functions were explained and provided to retrieve detailed information about the error. We hope you found this article helpful. Check out more Linux Hint articles for tips and information on SQL Servers.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list