Answer.

Any industrial solution on SQL requires a well-thought-out error handling architecture. Without logging and careful exception handling, it is impossible to debug complex processes, especially in stored procedures and batch scripts.

Background: Standard SQL allows minimal error handling (for example, RETURN and termination). Modern extensions (T-SQL, PL/pgSQL, PL/SQL, etc.) provide comprehensive error handling constructs (TRY/CATCH, EXCEPTION).

Problem: Without explicit error handling, "errors drown", and it is difficult for the administrator to determine the cause of a failure — especially during mass changes or when interacting with external systems. Often, there is a need to log errors in a separate table for subsequent analysis.

Solution: Use the arsenal of TRY/CATCH (T-SQL) or EXCEPTION (PL/pgSQL), as well as your own logging tables. Don't forget to send diagnostic information (error code, error text, request parameters, and time) to the log.

Example code (T-SQL, MS SQL Server):

CREATE TABLE ErrorLog (
    ErrorId INT IDENTITY PRIMARY KEY,
    ErrorTime DATETIME,
    ProcedureName NVARCHAR(128),
    ErrorMessage NVARCHAR(MAX),
    ErrorNumber INT,
    ErrorState INT,
    ErrorSeverity INT
);

CREATE PROCEDURE usp_ProcessOrders
AS
BEGIN
    BEGIN TRY
        -- Business logic
        UPDATE Orders SET Status = 'PROCESSED' WHERE Status = 'NEW';
    END TRY
    BEGIN CATCH
        INSERT INTO ErrorLog
        (
            ErrorTime, ProcedureName, ErrorMessage, ErrorNumber, ErrorState, ErrorSeverity
        )
        VALUES
        (
            GETDATE(),
            ERROR_PROCEDURE(),
            ERROR_MESSAGE(),
            ERROR_NUMBER(),
            ERROR_STATE(),
            ERROR_SEVERITY()
        );
        THROW;
    END CATCH
END

Example code (PL/pgSQL, PostgreSQL):

BEGIN
    -- Your code
EXCEPTION WHEN OTHERS THEN
    INSERT INTO error_log(ts, proc_name, err_text)
    VALUES(now(), 'my_proc', SQLERRM);
    RAISE;
END;

Key features:

Instant access to failure details for debugging.
Full traceability at all stages of the process.
Do not terminate execution without an explicit return and centralized logging.

Tricky questions.

Is it enough to catch the error and terminate the procedure without passing information outside?

No. Without explicit logging or propagating the error, it is impossible to capture and analyze the causes of the failure. It is important to either detail the error in the log or at least propagate it further (THROW/RAISE).

Is it possible to use only built-in SQL Server/DBMS logs to identify all errors in user procedures?

Partially. Many errors do not get logged by the server if they are "caught" and handled in the application or in procedures. For business logic, it is useful to maintain your event log with details.

Is it mandatory to use TRY/CATCH (or EXCEPTION) if the procedure only uses simple DML operations?

Mandatory, if the procedure affects important data, participates in critical chains, and needs to record non-standard situations. Even "safe" operations can lead to errors due to external constraints (uniqueness, FOREIGN KEY, deadlocks, etc.).

Common mistakes and anti-patterns

Not maintaining a separate error log at the application level.
Catching an error but not conveying information to the user/administrator.
Writing bulky handling blocks without templates — reduces readability.

Real-life example

Negative case

In the project, errors are not logged, only displayed to the user. During a mass failure, the administrator spends hours searching for the "invisible" problem.

Pros:

Simple solution, less code.

Cons:

Diagnosis is impossible.
No grounds for auditing and analyzing data quality.

Positive case

Any critical error is recorded in the log table with details (time, procedure, parameters, error code), and a ticket in the system references it.