How do you handle errors in SQL scripts?

Handling errors in SQL scripts is primarily done through error-checking functions, transaction control, and structured exception handling mechanisms provided by the database system. Most SQL dialects, such as T-SQL (SQL Server) or PL/pgSQL (PostgreSQL), include constructs like TRY...CATCH blocks or EXCEPTION handlers to capture and manage errors during script execution. These tools allow developers to define custom responses to errors, such as rolling back transactions, logging details, or providing user-friendly messages. Additionally, built-in functions like @@ERROR (SQL Server) or SQLSTATE (PostgreSQL) help identify specific error codes, enabling conditional logic based on the type of error encountered.

For example, in SQL Server, a TRY...CATCH block can wrap a set of statements to intercept errors. If an error occurs within the TRY block, control jumps to the CATCH block, where you can access error details using functions like ERROR_MESSAGE() or ERROR_SEVERITY(). Here’s a basic example:

BEGIN TRY
 INSERT INTO Employees (Name, Department) VALUES ('John', 'Sales');
 -- Intentional error: inserting duplicate key
 INSERT INTO Employees (ID, Name) VALUES (1, 'Jane');
END TRY
BEGIN CATCH
 SELECT ERROR_MESSAGE() AS ErrorMessage;
 ROLLBACK TRANSACTION;
END CATCH;

In this case, the second INSERT violates a primary key constraint, triggering the CATCH block to display the error message and roll back any changes. MySQL uses a different approach with DECLARE HANDLER statements to define actions for specific error codes, such as continuing execution or exiting after logging.

Best practices include using transactions to ensure data consistency, logging errors for debugging, and testing error scenarios thoroughly. Always wrap operations that modify data in transactions, so you can ROLLBACK if an error occurs. For instance, if a script transfers funds between accounts, both the withdrawal and deposit should succeed or fail together. Logging errors to a dedicated table with timestamps, error codes, and messages helps diagnose issues in production. Testing should simulate edge cases, like invalid inputs or constraint violations, to verify that error handling works as intended. For example, intentionally inserting invalid data into a table with strict constraints ensures your script gracefully handles the error instead of crashing.