Answer.

In stored procedures, error handling can and should be implemented using special constructs.

In SQL Server, the main mechanisms are TRY...CATCH blocks, where all errors within TRY are caught, and in CATCH, you can log the details. Functions like ERROR_NUMBER(), ERROR_MESSAGE() are available to get the details.

BEGIN TRY
    -- Risky operation
    UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
END TRY
BEGIN CATCH
    INSERT INTO ErrorLog(
        ErrorTime, ErrorNumber, ErrorMessage, UserName
    ) VALUES (
        GETDATE(), ERROR_NUMBER(), ERROR_MESSAGE(), SUSER_SNAME()
    );
    -- Additional recovery or ROLLBACK
END CATCH

In Oracle, EXCEPTION blocks are more commonly used:

BEGIN
    UPDATE ...;
EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO error_log VALUES (..., SQLERRM);
END;

Points to remember:

Not all errors are caught by CATCH, for example, parsing or compilation errors will abort the transaction before entering TRY.
For logging information, it's important to have a separate table for errors.
Log as much as possible: error text, number, username, operation parameters.
The syntax and capabilities differ across different DBMS.

Trick question.

Can an exception in the CATCH block lead to loss of the original error context? How to implement nested error handling?

Answer and example: If an error occurs in the CATCH block (for example, due to the unavailability of the ErrorLog table), the original error context is lost, and information about the cause of failure may be lost.

To safeguard against this, encapsulate logging in a separate procedure with its own TRY...CATCH to always catch "errors in the error handler."

BEGIN TRY
    -- main code
END TRY
BEGIN CATCH
    EXEC LogError @Error = ERROR_MESSAGE();
END CATCH
-- The LogError procedure itself contains its own TRY...CATCH

History

Project: Financial Reporting. In stored procedures, TRY...CATCH blocks were added, but we did not log the parameters with which the error occurred. As a result, during critical failure catches, it was necessary to manually search for the situation from the backup — the root cause was not obvious.

History

Project: Document Workflow Automation (Oracle). In the EXCEPTION block, we forgot to log the username. After a week of investigation, it was discovered that someone was intentionally "breaking" documents — we only found out through indirect signs in the audit log.

History

Project: E-commerce. The procedure wrote an error to ErrorLog on failure. Once the Log table was locked by a bloated transaction, and the logging attempt led to a nested error that overwrote the original cause and cleared the error stack. We fixed this by implementing an additional table for critical failures and multi-level logging.

How to implement effective error handling and debugging procedures in SQL stored procedures? What mechanisms are provided for catching and logging errors? Do these approaches differ in different DBMS?

Answer.

Trick question.