Error Handling in SQL: Managing Exceptions and Ensuring Reliable Transactions

Table of Contents

1. Introduction
2. Why Error Handling Matters in SQL
3. Types of Errors in SQL
4. Syntax Errors vs Runtime Errors
5. Understanding Transaction Errors
6. Rollbacks for Safe Error Recovery
7. Error Handling in SQL Scripts
8. Handling Errors in Stored Procedures
9. Using DECLARE HANDLER in MySQL
10. Handling Errors in PostgreSQL (EXCEPTION)
11. SQL Server’s TRY...CATCH Syntax
12. Identifying Errors with SQLSTATE and Error Codes
13. Logging Errors for Auditing and Debugging
14. Real-World Use Case: Bank Transfer with Error Handling
15. Preventing Errors Through Constraints
16. Retrying, Skipping, or Continuing After Errors
17. Common Error Patterns and Troubleshooting
18. Best Practices for Error Handling
19. Database-Specific Features for Error Management
20. Summary and What’s Next

1. Introduction

Error handling is a critical aspect of SQL programming. Whether you’re writing a script, stored procedure, or a high-throughput application backend, managing exceptions and transaction failures properly can protect your data and improve system reliability.

2. Why Error Handling Matters in SQL

• Maintains data consistency and integrity
• Prevents partial updates or corrupt states
• Helps diagnose and log application-level issues
• Enables clean recovery or user-friendly messages
• Supports robust and maintainable code

3. Types of Errors in SQL

4. Syntax Errors vs Runtime Errors

• Syntax errors are caught during query parsing.
• Runtime errors occur during execution and can be caught or handled in stored routines.

Example:

sqlCopyEditSELECT 1 / 0; -- Runtime error: division by zero

5. Understanding Transaction Errors

Many SQL statements are executed inside transactions. If one fails, the entire transaction must be rolled back to maintain data integrity.

6. Rollbacks for Safe Error Recovery

sqlCopyEditSTART TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Error occurs here
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

ROLLBACK; -- Ensures nothing is committed

7. Error Handling in SQL Scripts

Basic SQL scripts usually stop execution when an error is encountered. To control this, use database-specific constructs like:

• DECLARE HANDLER in MySQL
• BEGIN ... EXCEPTION in PostgreSQL
• TRY...CATCH in SQL Server

8. Handling Errors in Stored Procedures

MySQL:

sqlCopyEditDECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ROLLBACK;
  INSERT INTO error_log (message) VALUES ('Something failed');
END;

PostgreSQL:

sqlCopyEditBEGIN
  -- statements
EXCEPTION
  WHEN OTHERS THEN
    RAISE WARNING 'Error: %', SQLERRM;
END;

9. Using DECLARE HANDLER in MySQL

sqlCopyEditDECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  SET @has_error = TRUE;
END;

Handler types:

• CONTINUE: continue execution after error
• EXIT: exit the block
• UNDO: rarely supported

10. Handling Errors in PostgreSQL (EXCEPTION)

sqlCopyEditDO $$
BEGIN
  -- Attempt risky logic
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Divide by zero error';
  WHEN OTHERS THEN
    RAISE WARNING 'Unhandled error: %', SQLERRM;
END $$;

PostgreSQL provides GET STACKED DIAGNOSTICS for deeper debugging.

11. SQL Server’s TRY...CATCH Syntax

sqlCopyEditBEGIN TRY
  -- risky SQL
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE();
  ROLLBACK TRANSACTION;
END CATCH;

Also access: ERROR_NUMBER(), ERROR_LINE(), ERROR_SEVERITY()

12. Identifying Errors with SQLSTATE and Error Codes

Useful for creating custom error logic based on type.

13. Logging Errors for Auditing and Debugging

sqlCopyEditCREATE TABLE error_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  error_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  message TEXT
);

-- Log error
INSERT INTO error_log (message) VALUES ('Update failed at step 3');

14. Real-World Use Case: Bank Transfer with Error Handling

sqlCopyEditBEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    INSERT INTO error_log (message) VALUES ('Transfer failed');
  END;

  START TRANSACTION;

  UPDATE accounts SET balance = balance - 1000 WHERE id = 101;
  UPDATE accounts SET balance = balance + 1000 WHERE id = 202;

  COMMIT;
END;

15. Preventing Errors Through Constraints

Define constraints in schema:

• CHECK (quantity >= 0)
• NOT NULL to enforce required fields
• UNIQUE to prevent duplicates
• FOREIGN KEY to enforce relational integrity

16. Retrying, Skipping, or Continuing After Errors

Use CONTINUE HANDLER or write loop logic to skip faulty records during ETL.

sqlCopyEditDECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET @skipped_rows = @skipped_rows + 1;

17. Common Error Patterns and Troubleshooting

18. Best Practices for Error Handling

• Always wrap complex DML in transactions
• Log meaningful context with every failure
• Use specific error handling when possible
• Test edge cases (nulls, duplicates, failures)
• Avoid silent failure paths unless explicitly desired

19. Database-Specific Features for Error Management

20. Summary and What’s Next

Error handling in SQL empowers you to build robust, resilient, and fault-tolerant systems. Whether using handlers, exception blocks, or structured logs, it’s essential for modern database operations.