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
TEST1with two fields:T_IDandT_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_DEPENDwith two fields:TD_IDandTD_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— InsertD— 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.
