SQL Error Handling (TRY/CATCH / Exception blocks)

ADVERTISEMENT

When running SQL code, errors can happen — like constraint violations or syntax errors. Handling these errors gracefully lets you control what happens next instead of crashing your application or process.

🔹 Why Handle Errors?

  • Prevent your transaction from failing unexpectedly
  • Log or notify about issues
  • Retry operations or clean up resources

🔹 Common Error Handling Methods by DBMS

DBMSMethod
SQL ServerTRY…CATCH block
PostgreSQLBEGIN…EXCEPTION…END block
OracleBEGIN…EXCEPTION…END block
MySQLDECLARE HANDLER (limited)

🔹 Example: SQL Server TRY…CATCH

BEGIN TRY
    -- Attempt an operation
    INSERT INTO employees (id, name) VALUES (1, 'John');
END TRY
BEGIN CATCH
    -- Error handling logic
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
    ROLLBACK TRANSACTION;
END CATCH;

🔹 Example: Oracle PL/SQL Exception Handling

BEGIN
    INSERT INTO employees (id, name) VALUES (1, 'John');
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('Duplicate ID error.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Some other error occurred.');
END;

🔹 Best Practices

  • Always handle exceptions where failure is possible
  • Use specific exception/error codes when possible
  • Roll back transactions if needed
  • Log errors for debugging and auditing
  • Keep error messages user-friendly and secure

🧠 Quick Recap

PointExplanation
TRY…CATCH / EXCEPTIONBlocks to catch and handle errors
Specific ErrorsHandle known errors separately
RollbackUndo changes if error occurs
LoggingRecord errors for diagnosis

💡 Proper error handling makes your SQL code robust and your application reliable.

ADVERTISEMENT