Handling errors in SQL Server can feel like trying to find a typo in a 3000-line query—frustrating, time-consuming, and not the kind of adventure anyone signs up for. But, just like you wouldn’t skip error handling in your code (right?), it's essential in SQL to keep your data and your sanity safe.

Why Error Handling Matters

Picture this: You’re running a query, everything looks perfect, and BAM! Error. Your transaction fails, and you’re left staring at your screen wondering why the universe is out to get you. Enter: Try...Catch blocks. They help you catch those sneaky errors and allow your scripts to handle them gracefully.

Basic Try...Catch Structure

SQL Server's Try...Catch block works similarly to many programming languages. Here’s a simple example to catch errors when dividing by zero: 


BEGIN TRY
 -- Your code here
 SELECT 10 / 0;
END TRY
BEGIN CATCH
 PRINT 'Whoops! Something went wrong.';
 PRINT ERROR_MESSAGE();
END CATCH;

Now, instead of your script blowing up like a sequel to a Michael Bay movie, it catches the error and provides a friendly message.

Logging Errors for Future Reference

The problem with just catching errors is that it’s like covering your ears when someone shouts "fire"—sure, you didn’t hear the bad news, but it’s still there. Logging errors is the real superhero here. You can store error details into a logging table to review later (or present to your boss with confidence).Let’s build an error logging table and log the details of any error caught: 


-- Create database if it doesn't exist
IF DB_ID('ErrorLogDB') IS NULL
BEGIN
 CREATE DATABASE ErrorLogDB;
END

-- Use the database
USE ErrorLogDB;

-- Create error log table
IF OBJECT_ID('dbo.ErrorLog', 'U') IS NULL
BEGIN
 CREATE TABLE dbo.ErrorLog
 (
 ErrorLogID INT IDENTITY(1,1) PRIMARY KEY,
 ErrorMessage NVARCHAR(4000),
 ErrorSeverity INT,
 ErrorState INT,
 ErrorLine INT,
 ErrorProcedure NVARCHAR(200),
 ErrorTime DATETIME DEFAULT GETDATE()
 );
END

Now let’s modify the Try...Catch block to log errors into this table: 


BEGIN TRY
 -- Code that might fail
 SELECT 10 / 0;
END TRY
BEGIN CATCH
 -- Log the error into the ErrorLog table
 INSERT INTO dbo.ErrorLog (ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
 VALUES (ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE());

 PRINT 'Error logged successfully.';
END CATCH;

By logging your errors, you’re preparing for a future where you can track down what went wrong, even if your users aren’t exactly “descriptive” in their bug reports. ("It’s broken" doesn’t cut it, right?)

Automating the Log

You can set up stored procedures to handle the error logging process, making it even easier to keep your logs clean and consistent. 


CREATE PROCEDURE dbo.LogError
AS
BEGIN
 INSERT INTO dbo.ErrorLog (ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure)
 VALUES (ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE());
END;

Now, whenever you want to log an error: 


BEGIN TRY
 -- Risky code
 SELECT 10 / 0;
END TRY
BEGIN CATCH
 EXEC dbo.LogError;
END CATCH;

Wrapping It Up

Try...Catch blocks and proper error logging aren’t just nice-to-haves—they’re essential for maintaining robust SQL Server applications. You’ll thank yourself later when you're not scrambling through logs, wondering why something broke (again).Schedule a free consultation: Free 15-minute consultation See our pricing: Adroit DBA - SQL Performance, SQL Troubleshooting