THROW vs RAISERROR in SQL Server

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic table: LeaveRequests (business rules validated with THROW)

DROP TABLE IF EXISTS dbo.LeaveRequests;
DROP TABLE IF EXISTS dbo.LeaveBalance;

CREATE TABLE dbo.LeaveBalance
(
    EmployeeID INT PRIMARY KEY REFERENCES dbo.Employees(EmployeeID),
    CasualLeft INT NOT NULL,
    SickLeft   INT NOT NULL
);

INSERT INTO dbo.LeaveBalance (EmployeeID, CasualLeft, SickLeft) VALUES
(8, 5, 6), (9, 0, 4), (10, 8, 8), (12, 2, 1), (14, 10, 6);

CREATE TABLE dbo.LeaveRequests
(
    RequestID  INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT         NOT NULL,
    LeaveType  VARCHAR(10) NOT NULL,
    FromDate   DATE        NOT NULL,
    ToDate     DATE        NOT NULL,
    Days       AS (DATEDIFF(DAY, FromDate, ToDate) + 1),
    Status     VARCHAR(10) NOT NULL DEFAULT 'Pending'
);
GO

(5 rows affected)

Part A: THROW basics

THROW stops the batch. "After THROW" is never printed.

BEGIN TRY
    THROW 50001, 'Custom error raised by THROW.', 1;
    PRINT 'After THROW';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS Num, ERROR_SEVERITY() AS Sev, ERROR_STATE() AS State, ERROR_MESSAGE() AS Msg;
END CATCH;
GO

Dynamic message with THROW (build the text first)

DECLARE @EmpID INT = 99, @Msg NVARCHAR(2048);
SET @Msg = FORMATMESSAGE('Employee %d does not exist (checked at %s).', @EmpID, CONVERT(VARCHAR(19), SYSDATETIME(), 120));

BEGIN TRY
    IF NOT EXISTS (SELECT 1 FROM dbo.Employees WHERE EmployeeID = @EmpID)
        THROW 50002, @Msg, 1;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS Num, ERROR_MESSAGE() AS Msg;
END CATCH;
GO

Re-throw: THROW without parameters inside CATCH keeps the Original error number/line

BEGIN TRY
    BEGIN TRY
        INSERT INTO dbo.LeaveBalance (EmployeeID, CasualLeft, SickLeft) VALUES (8, 1, 1);   -- duplicate PK (2627)
    END TRY
    BEGIN CATCH
        PRINT 'Inner: logging and re-throwing...';
        EXEC dbo.usp_LogError;
        THROW;      -- re-throw
    END CATCH;
END TRY
BEGIN CATCH
    SELECT 'Outer caught' AS Info, ERROR_NUMBER() AS OriginalErrorNumber, ERROR_MESSAGE() AS Msg;
END CATCH;
GO

(0 rows affected)
Inner: logging and re-throwing...

Part B: RAISERROR basics

Printf-style parameters: %s = string, %d = integer

DECLARE @Name VARCHAR(50) = 'Rahul', @Left INT = 0;
RAISERROR('Employee %s has only %d casual leave(s) left.', 10, 1, @Name, @Left);   -- severity 10 = info message only
GO

Employee Rahul has only 0 casual leave(s) left.

Severity decides behaviour

0-10 : informational, NOT caught by CATCH, shown in Messages 11-16 : user errors, caught by CATCH 17-19 : resource / software errors 20-25 : fatal, need WITH LOG and sysadmin, closes connection

BEGIN TRY
    RAISERROR('Severity 10 - just info, CATCH is NOT fired', 10, 1);
    RAISERROR('Severity 16 - real error, CATCH fires', 16, 1);
    PRINT 'not reached';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS Num, ERROR_SEVERITY() AS Sev, ERROR_MESSAGE() AS Msg;   -- Num = 50000
END CATCH;
GO

Severity 10 - just info, CATCH is NOT fired

RAISERROR does NOT stop execution outside TRY (THROW does)

RAISERROR('RAISERROR: error shown, but the next line still runs', 16, 1);
PRINT 'I still run after RAISERROR';
GO

I still run after RAISERROR

Progress messages with with Nowait (shown immediately, PRINT waits for buffer)

DECLARE @i INT = 1;
WHILE @i <= 3
BEGIN
    RAISERROR('Processing step %d of 3 ...', 0, 1, @i) WITH NOWAIT;
    WAITFOR DELAY '00:00:01';
    SET @i += 1;
END;
GO

Processing step 1 of 3 ...
Processing step 2 of 3 ...
Processing step 3 of 3 ...

Part C: Custom Messages in sys.messages (optional, needs sysadmin/serveradmin)

-- Add a reusable message once per server. Safe to re-run.
IF EXISTS (SELECT 1 FROM sys.messages WHERE message_id = 60001)
    EXEC sys.sp_dropmessage @msgnum = 60001;

EXEC sys.sp_addmessage
     @msgnum   = 60001,
     @severity = 16,
     @msgtext  = N'Leave request rejected for employee %d: %s';
GO

BEGIN TRY
    RAISERROR(60001, -1, -1, 9, 'not enough casual leave');     -- -1 = use the stored severity / state
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS Num, ERROR_MESSAGE() AS Msg;
END CATCH;

-- With THROW, use FORMATMESSAGE to read the stored text
DECLARE @m NVARCHAR(2048) = FORMATMESSAGE(60001, 12, 'dates overlap');
BEGIN TRY
    THROW 60001, @m, 1;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS Num, ERROR_MESSAGE() AS Msg;
END CATCH;
GO

Part D: Real Example - business rule validation in a procedure

CREATE OR ALTER PROCEDURE dbo.usp_ApplyLeave
    @EmployeeID INT,
    @LeaveType  VARCHAR(10),
    @FromDate   DATE,
    @ToDate     DATE
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @Days INT = DATEDIFF(DAY, @FromDate, @ToDate) + 1,
            @Left INT,
            @Msg  NVARCHAR(2048);

    -- Rule 1 : valid leave type
    IF @LeaveType NOT IN ('Casual', 'Sick')
        THROW 50101, 'LeaveType must be Casual or Sick.', 1;

    -- Rule 2 : date order
    IF @ToDate < @FromDate
        THROW 50102, 'ToDate cannot be before FromDate.', 1;

    -- Rule 3 : employee must have a balance row
    SELECT @Left = CASE @LeaveType WHEN 'Casual' THEN CasualLeft ELSE SickLeft END
    FROM dbo.LeaveBalance WHERE EmployeeID = @EmployeeID;

    IF @Left IS NULL
    BEGIN
        SET @Msg = CONCAT('No leave balance found for employee ', @EmployeeID, '.');
        THROW 50103, @Msg, 1;
    END;

    -- Rule 4 : enough balance
    IF @Left < @Days
    BEGIN
        SET @Msg = CONCAT('Requested ', @Days, ' day(s) but only ', @Left, ' ', @LeaveType, ' leave(s) left.');
        THROW 50104, @Msg, 1;
    END;

    -- Rule 5 : no overlapping requests
    IF EXISTS (SELECT 1 FROM dbo.LeaveRequests
               WHERE EmployeeID = @EmployeeID AND Status <> 'Rejected'
                 AND @FromDate <= ToDate AND @ToDate >= FromDate)
        THROW 50105, 'Leave dates overlap with an existing request.', 1;

    BEGIN TRAN;
        INSERT INTO dbo.LeaveRequests (EmployeeID, LeaveType, FromDate, ToDate)
        VALUES (@EmployeeID, @LeaveType, @FromDate, @ToDate);

        UPDATE dbo.LeaveBalance
        SET CasualLeft = CasualLeft - CASE WHEN @LeaveType = 'Casual' THEN @Days ELSE 0 END,
            SickLeft   = SickLeft   - CASE WHEN @LeaveType = 'Sick'   THEN @Days ELSE 0 END
        WHERE EmployeeID = @EmployeeID;
    COMMIT TRAN;

    SELECT 'Leave applied' AS Result, @Days AS Days;
END;
GO

-- Test all rules : each call is wrapped so the script continues
DECLARE @tests TABLE (TestNo INT, EmpID INT, LType VARCHAR(10), FromD DATE, ToD DATE);
INSERT INTO @tests VALUES
(1, 8,  'Casual',  '2025-07-01', '2025-07-02'),   -- OK
(2, 8,  'Holiday', '2025-07-05', '2025-07-05'),   -- 50101 bad type
(3, 8,  'Casual',  '2025-07-10', '2025-07-08'),   -- 50102 bad dates
(4, 25, 'Sick',    '2025-07-01', '2025-07-01'),   -- 50103 no balance
(5, 9,  'Casual',  '2025-07-01', '2025-07-01'),   -- 50104 no casual leave left
(6, 8,  'Casual',  '2025-07-02', '2025-07-03');   -- 50105 overlap with test 1

DECLARE @n INT = 1, @e INT, @lt VARCHAR(10), @f DATE, @t DATE;
WHILE @n <= 6
BEGIN
    SELECT @e = EmpID, @lt = LType, @f = FromD, @t = ToD FROM @tests WHERE TestNo = @n;
    BEGIN TRY
        EXEC dbo.usp_ApplyLeave @e, @lt, @f, @t;
        PRINT CONCAT('Test ', @n, ': SUCCESS');
    END TRY
    BEGIN CATCH
        PRINT CONCAT('Test ', @n, ': ERROR ', ERROR_NUMBER(), ' - ', ERROR_MESSAGE());
    END CATCH;
    SET @n += 1;
END;

SELECT * FROM dbo.LeaveRequests;
SELECT * FROM dbo.LeaveBalance;
GO

(6 rows affected)

Test 1: SUCCESS
Test 2: ERROR 50101 - LeaveType must be Casual or Sick.
Test 3: ERROR 50102 - ToDate cannot be before FromDate.
Test 4: ERROR 50103 - No leave balance found for employee 25.
Test 5: ERROR 50104 - Requested 1 day(s) but only 0 Casual leave(s) left.
Test 6: ERROR 50105 - Leave dates overlap with an existing request.

Practice Questions

1. Write a procedure that THROWs 50201 when a product price is set below its CostPrice.
2. Use RAISERROR ... WITH NOWAIT to print progress while looping over all departments.
3. Show the difference: THROW vs RAISERROR(16) outside TRY - which line runs next?
4. Add rule 6 to usp_ApplyLeave : no leave on Sundays (DATENAME(WEEKDAY, ...)).