SQL Error Handling (TRY/CATCH / Exception blocks)
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
DBMS | Method |
---|---|
SQL Server | TRY…CATCH block |
PostgreSQL | BEGIN…EXCEPTION…END block |
Oracle | BEGIN…EXCEPTION…END block |
MySQL | DECLARE 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
Point | Explanation |
---|---|
TRY…CATCH / EXCEPTION | Blocks to catch and handle errors |
Specific Errors | Handle known errors separately |
Rollback | Undo changes if error occurs |
Logging | Record errors for diagnosis |
💡 Proper error handling makes your SQL code robust and your application reliable.