Introduction
Error handling is one of the most ignored parts of database development, especially in early-stage systems. Everything works fine during testing, but once the system goes live, exceptions occur: constraint violations, deadlocks, invalid data, permission issues, timeout errors, and unexpected system failures.
Without proper handling, these failures may:
Break business workflows
Insert partial or corrupted data
Rollback business transactions incorrectly
Cause inconsistent financial or inventory records
Make debugging extremely difficult
SQL Server provides a robust mechanism for error handling using the TRY...CATCH block. When used correctly with transactions, logging, and retry patterns, it creates predictable and safe database execution.
This article explains error handling using real-world patterns, practical examples, diagrams, and production-ready code.
The Business Case Study
Imagine a warehouse and inventory management system. When a user issues a stock transaction, the system:
Inserts a record into the movement history
Updates stock quantity
Logs the user action
If step 2 fails, but step 1 succeeded, your database now contains a partial update. This is called inconsistent state, which leads to downstream problems.
To prevent this, we need:
A reliable transaction block
Proper exception handling
Safe rollback
Optional logging and retry logic
What TRY…CATCH Does in SQL Server
TRY…CATCH works like exception handling in programming languages.
Syntax
BEGIN TRY
-- Code that may failEND TRY
BEGIN CATCH
-- Error handling codeEND CATCH
If any error occurs in the TRY block:
SQL stops executing remaining statements in TRY
Control moves to CATCH
You can log, rollback, or rethrow error
Error Flow Diagram
┌──────────────────────────┐
| Start |
└─────────────┬────────────┘
|
BEGIN TRY
|
┌────────▼─────────┐
| Execute SQL Code |
└────────┬─────────┘
|
Error?───┬────Yes─────┐
| |
No |
| |
┌──────▼─────┐ |
| Commit | |
└──────┬─────┘ |
| |
Success |
|
BEGIN CATCH
|
┌──────▼─────────┐
| Log / Rollback |
└──────┬─────────┘
|
End
Using TRY…CATCH with Transactions (Best Practice)
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO StockTransactions (StockId, Quantity, ActionType)
VALUES (1001, 5, 'ISSUE');
UPDATE Stock SET QuantityAvailable = QuantityAvailable - 5
WHERE StockId = 1001;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
INSERT INTO ErrorLog (ErrorMessage, ErrorNumber, ErrorTime)
VALUES (ERROR_MESSAGE(), ERROR_NUMBER(), GETDATE());
END CATCH;
This prevents partial data during failure.
Accessing Error Details Inside CATCH
SQL Server gives six built-in functions:
| Function | Description |
|---|
ERROR_MESSAGE() | Full message text |
ERROR_LINE() | Line number of failure |
ERROR_SEVERITY() | Severity level |
ERROR_NUMBER() | Error code |
ERROR_STATE() | Internal state |
ERROR_PROCEDURE() | Stored procedure name |
Example logging
SELECT
ERROR_MESSAGE() AS Message,
ERROR_LINE() AS LineNumber,
ERROR_NUMBER() AS Number,
ERROR_PROCEDURE() AS ProcedureName;
Real World Pattern: Logging with Retry Logic
Some errors are temporary, especially:
Retry approach
DECLARE @RetryCount INT = 0, @MaxRetries INT = 3;
WHILE (@RetryCount < @MaxRetries)
BEGIN
BEGIN TRY
BEGIN TRAN
-- business logic here
COMMIT TRAN
BREAK
END TRY
BEGIN CATCH
ROLLBACK TRAN
SET @RetryCount = @RetryCount + 1
IF @RetryCount = @MaxRetries
BEGIN
INSERT INTO ErrorLog (Message, RetryAttempts)
VALUES (ERROR_MESSAGE(), @RetryCount)
END
WAITFOR DELAY '00:00:02'
END CATCH
END
When TRY…CATCH Helps
| Failure Type | Handled? |
|---|
| Syntax errors | No |
| Constraint violations | Yes |
| Runtime validation errors | Yes |
| Divide-by-zero | Yes |
| Timeout or deadlock errors | Yes |
| Explicit RAISERROR or THROW | Yes |
Syntax errors must be corrected before execution.
THROW vs RAISERROR
Before SQL Server 2012, developers used RAISERROR.
Now Microsoft recommends THROW, as it:
Example
THROW;
Custom
THROW 50001, 'Insufficient quantity', 1;
Best Practices for Enterprise Systems
Always use TRY…CATCH with transaction-based operations.
Log errors using a structured table or monitoring tool (ELK, Grafana, Azure Log Analytics).
Use THROW instead of RAISERROR.
Retry only transient failures, not all failures.
Store meaningful messages for debugging.
Make stored procedures consistent: return success/failure codes.
Final Example: Production-Style Stored Procedure
CREATE PROCEDURE usp_IssueStock
@StockId INT,
@Qty INT,
@UserId INTASBEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
UPDATE Stock
SET QuantityAvailable = QuantityAvailable - @Qty
WHERE StockId = @StockId;
INSERT INTO StockHistory (StockId, Qty, ActionType, CreatedBy)
VALUES (@StockId, @Qty, 'ISSUE', @UserId);
COMMIT TRAN
SELECT 'SUCCESS' AS Status;
END TRY
BEGIN CATCH
ROLLBACK TRAN
INSERT INTO ErrorLog (Message, ProcedureName, DateTime)
VALUES (ERROR_MESSAGE(), ERROR_PROCEDURE(), GETDATE());
THROW;
END CATCH
END
Summary
TRY…CATCH is essential for building reliable, predictable, and failure-resilient database logic. It ensures errors are captured, logged, and handled gracefully without corrupting the database.
With proper use of transactions, THROW, and structured logging, SQL Server applications—whether ERP, banking, aviation, or e-commerce—remain consistent and stable even under failure conditions.