How do you handle errors and exceptions inside a stored procedure?

PostgreSQL (PL/pgSQL)

CREATE OR REPLACE PROCEDURE safe_transfer(from_acct INT, to_acct INT, amt NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amt WHERE id = from_acct;

    IF (SELECT balance FROM accounts WHERE id = from_acct) < 0 THEN
        RAISE EXCEPTION 'Insufficient funds in account %', from_acct
            USING ERRCODE = 'insufficient_funds';
    END IF;

    UPDATE accounts SET balance = balance + amt WHERE id = to_acct;

EXCEPTION
    WHEN insufficient_privilege THEN
        RAISE NOTICE 'Permission issue, transfer aborted';
        RAISE;   -- re-raise to the caller after logging
    WHEN OTHERS THEN
        RAISE NOTICE 'Unexpected error: %', SQLERRM;
        RAISE;   -- never silently swallow unknown errors
END;
$$;

SQL Server (T-SQL)

BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - @amt WHERE id = @from_acct;

    IF (SELECT balance FROM accounts WHERE id = @from_acct) < 0
        THROW 50000, 'Insufficient funds', 1;

    UPDATE accounts SET balance = balance + @amt WHERE id = @to_acct;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;   -- re-raise the original error to the caller
END CATCH;

Key principles

1. Roll back on failure. Any partial writes made before the error must be undone — leaving the transfer example half-applied (debited but not credited) is exactly the atomicity violation ACID exists to prevent. ROLLBACK (explicit, or via automatic transaction abort) is essential in the error path.
2. Catch specific conditions deliberately, don't blanket-swallow everything. A bare WHEN OTHERS (PL/pgSQL) or empty CATCH block that suppresses all errors silently hides real bugs and data problems — always at least log/re-raise unexpected errors rather than making them disappear.
3. Use meaningful, application-actionable error signals. Raising a specific error code/message (insufficient_funds, a custom THROW with a clear message) lets the calling application distinguish "the transfer failed because of insufficient funds" (a business-logic condition the app should show the user) from "the transfer failed because of a database bug" (an operational alert).
4. Re-raise unless you're specifically handling the condition. Catching an error to log it, then continuing as if nothing happened, is rarely correct — usually you want to log/handle and still propagate the failure so the caller (and any transaction it's part of) also reacts correctly.

This question tests whether a candidate treats database-level error handling with the same rigor as application-level error handling — expecting failures, ensuring atomicity is preserved on the failure path, and avoiding the anti-pattern of a catch-all block that silently discards information about what actually went wrong.