Error Handling in SQL Server: TRY CATCH Basics

BEGIN TRY
    -- code that may fail
END TRY
BEGIN CATCH
    -- runs only if there is an error
END CATCH

Useful functions inside CATCH:
  ERROR_NUMBER()  -> error code
  ERROR_MESSAGE() -> error text
  ERROR_LINE()    -> line where it failed

This lesson creates its own small tables, so you can run it on any SQL Server database. The Output under each query is the real result.

IF DB_ID('SqlBasicsDb') IS NULL
    CREATE DATABASE SqlBasicsDb;
GO

USE SqlBasicsDb;
GO

DROP TABLE IF EXISTS Wallet;
GO

CREATE TABLE Wallet
(
    WalletID INT PRIMARY KEY,
    Name     VARCHAR(50),
    Balance  INT CHECK (Balance >= 0)      -- balance can not go below 0
);

INSERT INTO Wallet VALUES (1, 'Ravi', 1000), (2, 'Sita', 500);
GO

(2 rows affected)

Without TRY CATCH - the error is shown in red

SELECT 10 / 0;

With TRY CATCH - we handle the error ourselves

BEGIN TRY
    SELECT 10 / 0;
END TRY
BEGIN CATCH
    PRINT 'Something went wrong!';
END CATCH;
GO

Something went wrong!

Show error details

BEGIN TRY
    SELECT 10 / 0;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER()  AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage,
           ERROR_LINE()    AS ErrorLine;
END CATCH;
GO

Duplicate primary key error

BEGIN TRY
    INSERT INTO Wallet VALUES (1, 'John', 300);     -- WalletID 1 already exists
END TRY
BEGIN CATCH
    PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH;
GO

(0 rows affected)
Error: Violation of PRIMARY KEY constraint 'PK__Wallet__84D4F92EC8D74803'. Cannot insert duplicate key in object 'dbo.Wallet'. The duplicate key value is (1).

Wrong data type error

BEGIN TRY
    SELECT CAST('abc' AS INT);
END TRY
BEGIN CATCH
    PRINT 'Cannot convert text to number';
END CATCH;
GO

Cannot convert text to number

CHECK constraint error (balance below 0)

BEGIN TRY
    UPDATE Wallet SET Balance = Balance - 5000 WHERE WalletID = 2;
END TRY
BEGIN CATCH
    PRINT 'Not enough balance';
END CATCH;
GO

(0 rows affected)
Not enough balance

Our own error with THROW

THROW error_number (50000 or more), 'message', state (use 1)

DECLARE @Amount INT = -50;

BEGIN TRY
    IF @Amount <= 0
        THROW 50001, 'Amount must be more than zero', 1;

    PRINT 'Amount is OK';
END TRY
BEGIN CATCH
    PRINT 'Error ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ': ' + ERROR_MESSAGE();
END CATCH;
GO

Error 50001: Amount must be more than zero

Our own error with RAISERROR (older way)

BEGIN TRY
    RAISERROR('This is my custom error', 16, 1);   -- 16 = normal error level
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();
END CATCH;
GO

This is my custom error

TRY CATCH with a TRANSACTION (most important use)

If any step fails, undo everything.

BEGIN TRY
    BEGIN TRANSACTION;

        UPDATE Wallet SET Balance = Balance + 2000 WHERE WalletID = 2;   -- works
        UPDATE Wallet SET Balance = Balance - 2000 WHERE WalletID = 1;   -- fails (only 1000)

    COMMIT;
    PRINT 'Transfer done';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Transfer failed, all changes undone: ' + ERROR_MESSAGE();
END CATCH;
GO

SELECT * FROM Wallet;     -- balances are unchanged (1000 and 500)

(1 row affected)
(0 rows affected)
Transfer failed, all changes undone: The UPDATE statement conflicted with the CHECK constraint "CK__Wallet__Balance__756D6ECB". The conflict occurred in database "SqlBasicsDb", table "dbo.Wallet", column 'Balance'.

Save errors into a log table

DROP TABLE IF EXISTS ErrorLog;
GO

CREATE TABLE ErrorLog
(
    LogID        INT IDENTITY(1,1),
    ErrorNumber  INT,
    ErrorMessage VARCHAR(4000),
    LogTime      DATETIME DEFAULT GETDATE()
);
GO

BEGIN TRY
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    INSERT INTO ErrorLog (ErrorNumber, ErrorMessage)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE());
END CATCH;
GO

SELECT * FROM ErrorLog;

(1 row affected)