Friday, September 4, 2026

SQL Server: Error Handling Using TRY...CATCH and Transactions

Error handling is an important part of database programming. When an operation fails because of a foreign key violation, duplicate key, or another database error, it is useful to return a meaningful error message to the application.

The following example demonstrates how to use TRY...CATCH, transactions, ERROR_NUMBER(), and RAISERROR to handle errors in a stored procedure. The example also shows how a foreign key dependency can prevent a record from being deleted.

Create the Primary Table

Create a table named TEST1 with two fields: T_ID and T_NAME.
CREATE TABLE TEST1
(
    T_ID INT IDENTITY(1,1) PRIMARY KEY,
    T_NAME VARCHAR(25)
);
GO

Create the Dependent Table

Create another table named TEST2_DEPEND with two fields: TD_ID and TD_ADDRESS.
CREATE TABLE TEST2_DEPEND
(
    TD_ID INT FOREIGN KEY REFERENCES TEST1(T_ID),
    TD_ADDRESS VARCHAR(25)
);
GO

Insert Records into TEST1

INSERT INTO TEST1 (T_NAME)
VALUES ('Rakesh');
GO

INSERT INTO TEST1 (T_NAME)
VALUES ('Kumar');
GO

INSERT INTO TEST1 (T_NAME)
VALUES ('Bardhan');
GO

Create a Stored Procedure for Insert and Delete

The following stored procedure accepts an operation type:

I — Insert
D — Delete

The procedure uses a transaction so that changes can be rolled back if an error occurs.

ERROR_NUMBER() is used to identify the error raised by SQL Server. For example, error 547 is commonly associated with a foreign key constraint violation.

CREATE PROCEDURE USP_TEST1
(
    @OP_TYP VARCHAR(3),
    @ID INT,
    @ADDRESS VARCHAR(30) = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        IF @OP_TYP = 'I'
        BEGIN
            INSERT INTO TEST1 (T_NAME)
            VALUES (@ADDRESS);
        END
        ELSE IF @OP_TYP = 'D'
        BEGIN
            DELETE FROM TEST1
            WHERE T_ID = @ID;
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        DECLARE @ERROR_NUMBER INT;

        SET @ERROR_NUMBER = ERROR_NUMBER();

        IF XACT_STATE() <> 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        IF @ERROR_NUMBER = 547
        BEGIN
            RAISERROR(
                'Cannot delete! The value has a dependency in another table.',
                16,
                1
            );
        END
        ELSE IF @ERROR_NUMBER = 2627
        BEGIN
            RAISERROR(
                'Cannot insert! A duplicate value already exists.',
                16,
                1
            );
        END
        ELSE
        BEGIN
            RAISERROR(
                'An error occurred while processing the database operation.',
                16,
                1
            );
        END
    END CATCH
END;
GO

Insert a Value into the Foreign Key Table

The following statement creates a dependency on the record with T_ID = 2.

INSERT INTO TEST2_DEPEND (TD_ID, TD_ADDRESS)
VALUES (2, 'Banaglore');
GO

Execute the Procedure

Now try to delete the record with T_ID = 2 from the primary table.

EXEC USP_TEST1 'D', 2;

Because TEST2_DEPEND contains a record that references TEST1.T_ID = 2, SQL Server raises a foreign key violation.

Expected Output

Cannot delete! The value has a dependency in another table.

The transaction is rolled back, so the failed delete does not change the data.

Checking SQL Server Error Messages

SQL Server provides system views that can be used to view available error messages and language information.

SELECT *
FROM sys.messages;

SELECT *
FROM sys.syslanguages;

Remarks

This example demonstrates how database errors can be handled inside a stored procedure and converted into more meaningful messages for the application.

Using transactions together with TRY...CATCH helps maintain data consistency when an operation fails. The ERROR_NUMBER() function can be used to identify the specific SQL Server error and take appropriate action.

For new development, THROW is generally preferred over RAISERROR for re-throwing or generating errors. This example uses RAISERROR because it demonstrates the approach used in the original article.

TechBytes

Menus