Some Random SQL Error Handling Techniques for Reliability and Transactional Integrity

Error handling is an important part of writing SQL code. Whether working with stored procedures, triggers, or transactions, proper error handling ensures that database operations remain reliable, maintainable, and capable of handling unexpected situations. In this write-up, I put together some very basic samples of SQL error handling, including TRY...CATCH blocks, logging, transaction management, as well as SAVEPOINT, XACT_ABORT, and stored procedure return codes.

For simplicity, the T-SQL code snippets in this write-up are provided for illustrative purposes only and do not necessarily reflect best coding practices.

1. Fundamentals of SQL Error Handling
TRY...CATCH Block
The TRY...CATCH block is the primary mechanism for handling errors in SQL Server. It allows capturing exceptions and responding appropriately. If an error occurs inside the TRY block, the CATCH block executes, returning error details in a select statement. If you need execution to stop, then you will need to throw the error instead on simply doing a select statement.

begin try
  -- your main sql code here
  -- your main sql code here
end try
begin catch
  select
  error_message() as ErrorMessage,
  error_number() as ErrorNumber,
  error_severity() as ErrorSeverity,
  error_state() as ErrorState
end catch

THROW and RAISEERROR
RAISEERROR allows you to generate custom error messages with specified severity levels.

THROW is used to re-throw captured errors and halt execution.

The point of this code is to demonstrate conditional logic before raising an error in SQL Server using RAISERROR with TRY...CATCH error handling.

-- example of conditional logic before raising an error
declare @somecondition int = 1 -- this will cause the error to be thrown

begin try
  -- conditionally raise an error
  if @somecondition = 1 -- if @somecondition <> 1 then error would not be thrown
  begin
    raiserror('my custom error occurred due to some condition.', 16, 1) -- raise error
  end

  -- your main sql code here
  select 'hello there'
  select 'no error occurred'
end try
begin catch
  -- capture the error and re-throw it
  throw  -- re-throw the error
end catch

Breaking Down the Concept of "Rethrow"

1. An error occurs in the TRY block.
2. If @somecondition = 1, the THROW statement inside TRY raises an error.
3. Execution immediately jumps to the CATCH block.
4. The CATCH block captures the error.
5. SQL Server automatically provides access to error details (ERROR_MESSAGE(), ERROR_NUMBER(), etc.).
6. Just handling the error in CATCH does not automatically stop execution as it needs to be re-raised if necessary.
7. Using THROW in CATCH (Re-throwing the error)
8. Instead of logging the error or handling it silently, THROW raises the exact same error again.
9. This allows the error to propagate up to the caller (e.g., another stored procedure or application).
10. No need to pass error details manually because SQL Server remembers the error context.

Why "Re-throw" Instead of Just "Throw"?

• The term "throw" generally refers to raising a new error.
• The term "rethrow" is used when you catch an existing error and raise it again.

When to Use Rethrow?

• When you don’t want to change the original error.
• When you want the error to propagate up the call stack as if the TRY...CATCH was not there.
• When handling errors in nested stored procedures or transaction rollback scenarios.

An alternative version of the SQL code using just THROW without RAISERROR

declare @somecondition int = 1 -- this will cause the error to be thrown

begin try
  -- conditionally throw an error
  if @somecondition = 1 -- if @somecondition <> 1 then the error would not be thrown
  begin
    throw 50001, 'my custom error occurred due to some condition.', 1
  end

  -- your main sql code here
  select 'hello there'
  select 'no error occurred'
end try
begin catch
  -- capture the error and re-throw it
  throw  -- re-throw the error
end catch

Key Differences Between THROW and RAISERROR

1. Simpler Syntax:
2. THROW 50001, 'My custom error occurred due to some condition.', 1
3. No need to specify severity (16) like in RAISERROR.
4. Built-in THROW for Re-Raising Errors:
5. THROW inside CATCH automatically re-throws the caught error without needing parameters.
6. RAISERROR requires capturing and re-raising the original error manually.
7. Always Uses Severity 16+:
8. With THROW, the severity must be 50000 or higher, which is the custom error range for SQL server.
9. RAISERROR allows you to specify lower severity levels.

When to Use THROW Instead of RAISERROR?

• When you need simpler and cleaner error handling.
• When you don't need advanced formatting. RAISERROR supports message substitution using placeholders.
• When using TRY...CATCH blocks, THROW is the preferred way to re-raise exceptions.

2. Transaction Management and Atomicity
Using TRY...CATCH with Transactions
Transactions in SQL Server guarantee atomicity, meaning that all operations within a transaction either succeed as a group or fail as a group. If any part of the process fails, the system rolls back to its original state, preventing partial updates and ensuring database integrity.

Using transactions is crucial when executing multiple SQL statements that must either fully complete or not execute at all to maintain consistency.

Basic Transaction with Error Handling
This sample code shows how a transaction ensures atomicity. If a specific condition is met, an error is thrown which causes the transaction to roll back:

declare @somecondition int = 1 -- this will cause the error to be thrown

begin transaction
begin try
  if @somecondition = 1 -- if @somecondition <> 1 then the error would not be thrown
  begin
    throw 50001, 'my custom error occurred due to some condition.', 1
  end

  -- sql operations (insert, update, delete, etc.)
  commit transaction
end try
begin catch
  rollback transaction
  throw  -- re-throw the error
end catch

If any SQL statement inside the TRY block fails, SQL Server immediately jumps to the CATCH block. The CATCH block rolls back the transaction, undoing any changes made since BEGIN TRANSACTION, thereby preventing partial updates.

Logging Errors in Transactions
Adding error logging to the CATCH block can help track failures and diagnose issues effectively.

begin transaction
begin try
  -- some sql operations (insert, update, delete, etc.)
  commit transaction
end try
begin catch
    rollback transaction

    -- log error details to a custom audit table
    insert into errorlog (errormessage, errorprocedure, errorline, errortime)
    values (ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE(), GETDATE())

    select ERROR_MESSAGE() as errormessage
end catch

Transactions are essential for atomicity and when performing multiple dependent SQL operations. ROLLBACK ensures data consistency by reverting changes in case of failure and the TRY…CATCH block allows for logging errors and prevents partial updates. This is important in all operations that interact with SQL and where data integrity is crucial.

Transactions ensure atomicity, which means that when performing multiple dependent SQL operations, they all must succeed together or fail together. ROLLBACK maintains consistency by reverting changes in case of failure and the TRY…CATCH block handles errors effectively while allowing for error logging and preventing partial updates. Logging errors provides valuable debugging information, ensuring better system reliability.

Transactions are essential in any SQL operation where data integrity is critical, ensuring that changes to the database remain consistent and reliable even in the event of failures.

Using SAVEPOINT for Partial Transaction Rollbacks
SAVEPOINT allows rolling back specific parts of a transaction while keeping others intact. In SQL Server, SAVEPOINT provides finer control over transactions by allowing partial rollbacks. Unlike a full ROLLBACK TRANSACTION, which undoes all changes since BEGIN TRANSACTION, SAVEPOINT enables rolling back only a portion of the transaction while keeping the rest intact. This is useful when handling multiple operations where only certain parts should be undone in case of an error.

SAVEPOINT can help preserve successful operations so if an error occurs, you can roll back to the last SAVEPOINT rather than discarding the entire transaction. This can allow for finer control when handling failures within a large transaction which can be helpful when dealing with dependent operations where certain steps should remain committed even if a later step fails and is rolled back.

begin transaction
begin try
  -- insert new record into the orders table
  insert into orders (orderid, customername, orderdate)
  values (101, 'John Doe', getdate())

  -- define savepoint before inserting a new
  -- record into the orderdetails table
  save transaction OrderDetailsSavepoint

  -- insert new record into the orderdetails table
  insert into orderdetails (orderid, productid, quantity)
  values (101, 1, 5)

  -- simulate an error
  throw 50001, 'simulated error occurred after OrderDetails insertion.', 1

  commit transaction
end try
begin catch
  select 'error. rolling back to OrderDetailsSavepoint savepoint.'

  -- rollback only the changes made after the savepoint
  rollback transaction OrderDetailsSavepoint

  select 'continuing with remaining transaction...'

  -- you can still commit other successful operations
  commit transaction
end catch

If a transaction is fully rolled back (full ROLLBACK TRANSACTION), all SAVEPOINTS within it are also discarded. Using SAVEPOINT does not release locks until the full transaction is committed or fully rolled back.

Using XACT_ABORT for Automatic Rollbacks
XACT_ABORT is a session-level (entire transaction) setting in SQL Server that automatically rolls back the entire transaction if a runtime error occurs. This eliminates the need for explicit error handling with ROLLBACK TRANSACTION which can help ensure data integrity with minimal code. It is very useful for bulk inserts, bulk updates and other "batch" type operations as it prevents partial updates in large batch operations.

XACT_ABORT ensures full rollback on failure. If any statement inside a transaction fails, the entire transaction is automatically rolled back. You do not need explicit BEGIN CATCH and ROLLBACK TRANSACTION with every transaction (reduces manual error handling).

set xact_abort on -- turn it on
-- do not need to turn off xact_abort
-- because it applies to the current session or batch
-- after the session ends or a new connection is made
-- xact_abort resets to its default state (off).

begin transaction
begin try
  -- insert new record into the orders table
  insert into orders (orderid, customername, orderdate)
  values (102, 'Jane Doe', getdate())

  -- simulate an error
    insert into orderdetails (orderid, productid, quantity)
    values (102, NULL, 5)  -- NULL is not allowed for ProductID

    commit transaction  -- this will not run if an error occurs
end try
begin catch
    select 'error. xact_abort automatically rolled back the transaction.'
end catch

3. Error Logging and Reporting
Logging Errors to a Table
Storing error details in a log table helps with troubleshooting and debugging.

begin catch
  insert into errorlog (errormessage, errornumber, errorseverity, errorstate)
  values (ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE())
end catch

It couldn't hurt to index the errorlog table for better query performance and to add other logging information such as SESSION_USER, APP_NAME(), HOST_NAME(), etc. to help make debugging easier and to add more context. If the error occurs in a stored procedure, then adding ERROR_PROCEDURE() and ERROR_LINE() for good measure might also be helpful.

Using Output Parameters for Error Reporting
Stored Procedures can return error messages via an output parameter.

drop procedure if exists dbo.MyProcedure
go

create procedure dbo.MyProcedure @ErrorMessage nvarchar(4000) output
as
begin
  begin try
    -- sql logic
  end try
  begin catch
    set @errormessage = error_message() -- return the error message to the calling code
  end catch
end
go

Conclusion
Using SQL error handling techniques ensures database reliability and maintainability. Using TRY...CATCH, transaction management, and logging to a table can help improve system resilience, handle unexpected situations effectively and maintain system integrity.