Advanced TRY CATCH in SQL Server for Error Control

Introduction: Error Handling in SQL Server

In database applications, errors are inevitable — invalid data, constraint violations, deadlocks, and connection drops can all occur during execution. Without proper handling, such errors can crash applications or leave data inconsistent.

To manage this gracefully, SQL Server provides a TRY…CATCH mechanism similar to C#’s Try…Catch blocks. It allows developers to execute SQL statements, catch errors, and respond appropriately — such as logging the issue or rolling back a transaction.

Understanding TRY…CATCH in SQL Server

What Is TRY…CATCH in SQL Server?

TRY…CATCH is a structured method for error handling introduced in SQL Server 2005. It lets you wrap SQL statements inside a TRY block. If an error occurs, SQL Server jumps to the CATCH block to handle the exception.

Why Error Handling Is Important

• Prevents abrupt termination of scripts.
• Ensures transactions maintain data integrity.
• Provides meaningful error information for debugging.
• Enables recovery or rollback mechanisms.

TRY…CATCH Syntax in SQL Server

General Structure

BEGIN TRY
    -- SQL statements that might cause an error
END TRY
BEGIN CATCH
    -- Code to handle the error
END CATCH;

Step-by-Step Explanation of Syntax

1. BEGIN TRY / END TRY
   Contains the SQL code you want to monitor for exceptions.
2. BEGIN CATCH / END CATCH
   Executes only if an error occurs inside the TRY block.
3. Error Functions
   Used to retrieve details like error number, severity, and message.

Example: Implementing TRY…CATCH in SQL Server

Creating the Sample Table

CREATE TABLE employeedetails (
    id INT IDENTITY(1,1),
    empsignum NVARCHAR(20),
    empFname NVARCHAR(100),
    empEname NVARCHAR(100),
    empdate DATETIME
);

Writing a Stored Procedure with TRY…CATCH

CREATE PROCEDURE Demo_exceptionhandling
(
    @empsignum NVARCHAR(20),
    @Fname NVARCHAR(100),
    @Ename NVARCHAR(100)
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        IF EXISTS (SELECT 1 FROM employeedetails WHERE empsignum = @empsignum)
            UPDATE employeedetails
            SET empFname = @Fname, empEname = @Ename
            WHERE empsignum = @empsignum;
        ELSE
            INSERT INTO employeedetails (empsignum, empFname, empEname, empdate)
            VALUES (@empsignum, @Fname, @Ename, GETDATE());
    END TRY
    BEGIN CATCH
        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;

    SET NOCOUNT OFF;
END;

Explanation of the Example Code

• The TRY block contains logic to insert or update employee details.
• If an exception (like constraint violation) occurs, control moves to the CATCH block.
• The CATCH block retrieves and displays detailed error information using built-in functions.

SQL Server Error Functions in the CATCH Block

Function	Description
ERROR_NUMBER()	Returns the unique error number.
ERROR_SEVERITY()	Returns how serious the error is (1–25).
ERROR_STATE()	Returns a numeric state identifying the error’s context.
ERROR_PROCEDURE()	Returns the stored procedure name where the error occurred.
ERROR_LINE()	Returns the exact line number of the error.
ERROR_MESSAGE()	Returns the full text of the error message.

These functions are invaluable for debugging and error logging.

TRY…CATCH with Transactions

Why Combine Transactions with TRY…CATCH?

In database operations, transactions group statements so they commit or roll back together.
If an error occurs, the transaction should be rolled back to maintain data consistency — which is easily handled with TRY…CATCH.

Example: Using TRY…CATCH with BEGIN TRANSACTION

CREATE PROCEDURE Demo_exceptionhandling
(
    @empsignum NVARCHAR(20),
    @Fname NVARCHAR(100),
    @Ename NVARCHAR(100)
)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    BEGIN TRY
        IF EXISTS (SELECT 1 FROM employeedetails WHERE empsignum = @empsignum)
            UPDATE employeedetails
            SET empFname = @Fname, empEname = @Ename
            WHERE empsignum = @empsignum;
        ELSE
            INSERT INTO employeedetails (empsignum, empFname, empEname, empdate)
            VALUES (@empsignum, @Fname, @Ename, GETDATE());
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage,
            GETDATE() AS ErrorTime;
    END CATCH;

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;

    SET NOCOUNT OFF;
END;

Step-by-Step Explanation

1. The procedure begins a transaction using BEGIN TRANSACTION.
2. The TRY block performs insert or update operations.
3. If an error occurs, SQL Server jumps to the CATCH block, where the transaction rolls back.
4. If successful, the transaction is committed.

This ensures data consistency even in case of runtime errors.

Limitations of TRY…CATCH in SQL Server

TRY…CATCH cannot handle certain conditions.

Unhandled Condition	Description
Severity ≤ 10	Informational messages or warnings.
Severity ≥ 20	Critical system-level errors (connection termination).
Attentions	User interrupts (e.g., Ctrl+C) or KILL command.
Compile Errors	Syntax errors that prevent batch execution.
Recompilation Errors	Occur during deferred name resolution.

When such errors occur, SQL Server skips the CATCH block entirely.

Best Practices for Using TRY…CATCH

✅ Log Errors for Auditing
Insert error details into a dedicated log table for future analysis.

✅ Use XACT_STATE()
This function helps determine whether the transaction can be safely committed or rolled back.

✅ Keep CATCH Lightweight
Avoid complex logic inside the CATCH block; focus on cleanup and logging.

✅ Test Error Scenarios
Always simulate possible errors (like PK violations) to validate your handling logic.

FAQs on TRY…CATCH in SQL Server

1. Can I Nest TRY…CATCH Blocks?

Yes. Nested TRY…CATCH blocks allow handling errors at multiple levels (e.g., inside subprocedures).

2. Does TRY…CATCH Handle Syntax Errors?

No. Syntax or compilation errors prevent execution of the TRY block entirely.

3. What’s the Difference Between TRY…CATCH and @@ERROR?

@@ERROR captures error codes line by line, while TRY…CATCH provides structured error management.

4. How Can I Log Errors Automatically?

Create an ErrorLog table and insert data from error functions inside your CATCH block.

5. What Happens When Severity > 20?

SQL Server terminates the connection; CATCH does not execute.

6. How Does TRY…CATCH Work with Dynamic SQL?

Wrap the EXEC sp_executesql statement inside TRY…CATCH to catch runtime errors.

Conclusion: Making SQL Server Applications More Reliable

The TRY…CATCH error handling mechanism in SQL Server is a powerful tool for managing exceptions, maintaining data integrity, and ensuring reliability in multi-user environments.

By combining TRY…CATCH with transactions and logging, developers can build robust, fault-tolerant stored procedures that prevent data corruption and simplify debugging.