Exception Handling in SQL Server : TRY-CATCH

What is Exception Handling?

Exception handling is the ability to deal with errors that occur or can occur on a database. These errors are called as exceptions.
To assist you with handling exceptions thrown by your SQL statements, you can start with a section as follows:

Syntax

The syntax is very similar to try-catch in programming languages.

BEGIN TRY
    --
    -- Your Code that can throw an exception
    --
END TRY
    -- Don't include any SQL statements here
BEGIN CATCH
    --
    -- Handle the exception here
    --
END CATCH

In case your code can throw an exception, include your normal code in a try block. Now, If an error occurs in the try block, you can use the catch block to display a message.
Note: A catch block gets executed only if an exception is generated in try block.

Example:

BEGIN TRY
    DECLARE @Number tinyint
    SET @Number = 50000;
    SELECT @Number AS Number
END TRY
BEGIN CATCH
    PRINT 'There is an error in your code' --This will be printed in output message window
    PRINT ERROR_MESSAGE(); -- This will print the type of error occurred
END CATCH

To give you a message related to the error, SQL provides the ERROR_MESSAGE() function.

Diagram




On the other hand, if no error occurs in the try block, that try block executes but when it ends, the execution skips the catch block and continues execution with code below the END CATCH line, if any.
------------------------------------------------------------------------------

Hope this blog clears you with use of try-catch in SQL Server.
Enjoy Learning...