Introduction :

In this tutorial, you will learn Error handling in SQL Server with TRY CATCH blocks in stored procedures.

Error handling in SQL Server give us control over Transact-SQL code. An error condition during a program execution is called an exception and the mechanism for resolving such an exception is known as an exception handler. SQL Server provides TRY, CATCH blocks for exception handling. We can put all T-SQL statements into a TRY BLOCK and the code for exception handling can be put into a CATCH block.

SQL Server TRY CATCH :

To use the TRY CATCH, you first place a group of Transact-SQL statements that could cause an exception in a BEGIN TRY…..END TRY block as follows:

BEGIN TRY  
   -- statements that cause exceptions
END TRY 

Then you use a BEGIN CATCH…END CATCH block immediately after the TRY block:

BEGIN CATCH  
   -- statements that handle exception
END CATCH  

The following syntax is complete TRY CATCH block :

BEGIN TRY
-- statements that cause exceptions
END TRY
BEGIN CATCH
-- statements that handle exception
END CATCH

Anything between the BEGIN TRY and END TRY is the code that we want to monitor for an error. So, if an error would have happened inside this TRY statement, the control would have immediately get transferred to the CATCH statement and then it would have started executing code line by line

Now, inside the CATCH statement, we can try to fix the error, report the error or even log the error so we know when it happened, who did it by logging the username, all the useful stuff. We even have access to some special data only available inside the CATCH statement

The CATCH block functions :

• ERROR_LINE() : returns the line number on which the exception occurred
• ERROR_NUMBER() : returns the number of the error that occurred.
• ERROR_MESSAGE() : returns the complete text of the generated error message.
• ERROR_STATE() : returns the state number of the error that occurred.
• ERROR_PROCEDURE() : returns the name of the stored procedure or trigger where the error occurred.
• ERROR_SEVERITY() : returns the severity level of the error that occurred.

Nested TRY CATCH Block

BEGIN TRY
    ---the statements that cause exceptions
END TRY
BEGIN CATCH
    --the statements That handle exception
    BEGIN TRY
        ---the nested TRY block
    END TRY
    BEGIN CATCH
        --- the nested CATCH block
    END CATCH
END CATCH

SQL Server TRY CATCH  block example

CREATE PROC sp_logic(
    @VarA decimal,
    @VarB decimal,
    @VarC decimal output
) AS
BEGIN
    BEGIN TRY
        SET @VarC = @AVar / @VarB;
    END TRY
    BEGIN CATCH
        SELECT  
            ERROR_NUMBER() AS ErrorNumber  
            ,ERROR_PROCEDURE() AS ErrorProcedure 
            ,ERROR_SEVERITY() AS ErrorSeverity 
            ,ERROR_MESSAGE() AS ErrorMessage; 
            ,ERROR_STATE() AS ErrorState  
            ,ERROR_LINE() AS ErrorLine    
    END CATCH
END;
GO

In this stored procedure, we have placed the formula inside the TRY block and called the CATCH block functions ERROR_*  inside the CATCH block, if error occurs in Try block then it handled in catch block.

SEE MORE