SQL Server Error Handling

In this post we are going to learn about exception handling features of T-SQL (also called Translate SQL). T-SQL has many procedural programming language features including error handling capabilities.  T-SQL program can throw exception because of many reasons and it is the responsibility of the SQL developer to handle the exceptions gracefully so that it is user friendly.

Handling Exceptions in T-SQL

Handling exceptions gracefully in T-SQL refers to the practice of anticipating and managing errors or exceptional situations that may occur during the execution of a T-SQL script or batch. Instead of allowing errors to propagate unchecked, graceful exception handling involves taking appropriate actions to respond to errors, providing meaningful error messages, and possibly taking corrective measures to ensure that the system remains in a consistent state.

Features of Handling Exceptions

Here are some key aspects of handling exceptions gracefully in T-SQL:

• Use TRY...CATCH Blocks
• Capture and Display Error Information
• Logging and Notification
• Rollback Transactions

Use TRY...CATCH Blocks:

T-SQL provides the TRY...CATCH construct for structured error handling. Code that is anticipated to raise an error is enclosed in a TRY block, and error-handling logic is defined in the associated CATCH block. Error handling means what to do after occurrence of error. We can display informative message, log the error details; trigger some action or rollback actions etc. in CATCH block.

BEGIN TRY
    -- Code that might raise an error
END TRY
BEGIN CATCH
    -- Error-handling logic
END CATCH

We should use only that piece of code within TRY block which might throw error. The reasons are as follows.

Better Code Readability:

Placing only the error-prone code within the TRY block makes the code more readable and maintainable. It clearly indicates the specific areas of the code that require special attention for error handling.

Minimizing Impact:

Limiting the scope of the TRY block to only the potentially problematic code helps in minimizing the impact of errors. If an error occurs, it won't affect the rest of the batch or procedure outside the TRY block.

Avoiding Unintended Rollbacks:

If a ROLLBACK statement is encountered within a CATCH block, it could inadvertently roll back the entire batch or transaction. By keeping the TRY block focused on specific code, we can ensure that only the intended actions are rolled back in case of an error.

Focused Error Handling:

By enclosing only the error-prone code in the TRY block, we can focus our error-handling logic on the specific statements that might encounter issues. This helps in providing more targeted and meaningful error messages.

In summary, using the TRY...CATCH construct with only the error-prone code inside the TRY block allows for more effective and targeted error handling, reduces the impact of errors, and improves the overall readability of the code.

Capture and Display Error Information:

Within the CATCH block, we can use functions like ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(), and ERROR_PROCEDURE() to capture detailed information about the error. This information can be logged or displayed to aid in troubleshooting.

BEGIN CATCH
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH

Logging and Notification:

Graceful exception handling often involves logging error details to a log table, file, or an external logging system. This helps DBA and SQL developers identify and address issues proactively. Notifications, such as sending emails or alerts, can also be triggered based on specific error conditions.

BEGIN CATCH
    -- Log error details to an error log table
INSERT INTO ErrorLog (ErrorMessage, ErrorTime) VALUES (ERROR_MESSAGE(), GETDATE());
    -- Send email notification
EXEC msdb.dbo.sp_send_dbmail @recipients = 'admin@example.com', @subject = 'Error Occurred', @body = ERROR_MESSAGE();
END CATCH

Rollback Transactions:

If a transaction is in progress when an error occurs, it's important to roll back the transaction to maintain the consistency of the database.

BEGIN TRY
    BEGIN TRANSACTION;
    -- Code that might raise an error
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

Provide User-Friendly Messages:

If the T-SQL code interacts with users, providing user-friendly error messages can improve the user experience. Customize error messages to be informative and helpful.

BEGIN TRY
    -- Code that might raise an error
END TRY
BEGIN CATCH
    PRINT 'An error occurred. Please contact support.’
END CATCH

By handling exceptions gracefully, you make your T-SQL code more robust and user-friendly, ensuring that unexpected issues are identified, logged, and addressed in a way that minimizes the impact on users and the overall system.

Difference between @@ERROR and RAISEERROR()

@@ERROR: It is a system variable in T-SQL that returns the error number generated by the last executed statement. After executing a statement, you can check the value of @@ERROR to determine if an error occurred and, if so, retrieve the error number. If error does not occur then @ERROR is 0.

Example

INSERT INTO Employee VALUES (1, 'Bush', 'Goerge');
IF @@ERROR <> 0
BEGIN
    PRINT 'An error occurred: ' + CAST(@@ERROR AS NVARCHAR(10));
END

RAISEERROR(msg_str, severity, state, ) is a T-SQL function used to generate error. The error message and its state and severity levels are given as arguments. It is often used within a CATCH block to provide custom error messages and control the behavior of error handling.

Pseudo code:

BEGIN TRY
    -- Code that might raise an error e.g. SELECT 1/0
END TRY

BEGIN CATCH
    -- Custom error message with RAISEERROR
    RAISEERROR (‘an error occurred.’ 16, 1);
END CATCH

Example

BEGIN TRY
     SELECT 1/0
END TRY

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Custom error message with RAISEERROR

    RAISERROR (@ErrorMessage,
               @ErrorSeverity,
               @ErrorState
               );
END CATCH

Different system functions to capture error in T-SQL

In T-SQL, there are several system functions and system views that you can use to capture and retrieve information about errors that occur during the execution of a batch or a stored procedure. Here are some key functions and views related to error handling:

• ERROR_MESSAGE()
• ERROR_NUMBER()
• ERROR_STATE()
• ERROR_SEVERITY()
• ERROR_PROCEDURE()
• ERROR_LINE()

ERROR_MESSAGE():

It returns the complete text of the error message generated by the last statement that caused an error. This function is often used in CATCH blocks to capture and display error messages.

BEGIN TRY
    -- Code that might raise an error
END TRY

BEGIN CATCH
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH

ERROR_NUMBER():

It returns the number of the error that occurred. This can be useful for identifying specific error conditions.

BEGIN CATCH
    IF ERROR_NUMBER() = 1205
        PRINT 'Deadlock detected.';
    ELSE
        PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH

ERROR_STATE():

It returns the state number of the error. This can be used to differentiate between different errors states associated with a single error number.

BEGIN CATCH
    IF ERROR_STATE() = 1
        PRINT 'This is a state 1 error.';
    ELSE
        PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH

ERROR_SEVERITY():

It returns the severity level of the error. Severity levels range from 0 to 25. Errors with severity levels 0-10 are considered informational, 11-16 are considered warnings, and 17-25 are considered errors.

BEGIN CATCH
    IF ERROR_SEVERITY() > 16
        PRINT 'This is a serious error.';
    ELSE
        PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH

ERROR_PROCEDURE():

It returns the name of the stored procedure or trigger where the error occurred. This can be useful for identifying the location of the error in your code.

BEGIN CATCH
    PRINT 'Error in procedure: ' + ERROR_PROCEDURE();
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH

ERROR_LINE():

It returns the line number at which the error occurred.

BEGIN CATCH
    PRINT 'Error on line: ' + CAST(ERROR_LINE() AS NVARCHAR(10));
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH

These functions provide detailed information about errors, allowing you to customize error handling and logging based on specific error conditions. They are typically used within CATCH blocks to capture error information and take appropriate actions. Additionally, the sys.messages system view can be queried for additional details about error messages.

In the next post we will learn about Stored Procedures in SQL Server.