SQL Error Handling: TRY-CATCH, RAISERROR & THROW

Note on syntax: Error handling varies significantly between database systems. TRY-CATCH is SQL Server syntax. MySQL uses DECLARE HANDLER. PostgreSQL uses BEGIN...EXCEPTION. We'll cover all three approaches so you can apply this to whichever DBMS you use.

1 Introduction to SQL Errors

Top

Errors in SQL happen when a statement can't be executed — a constraint is violated, a table doesn't exist, a data type is wrong, or a deadlock occurs. Without proper error handling, your stored procedures will crash silently, leaving data in an inconsistent state.

Common Error Categories

Category	Example	Error Code (MySQL)
Syntax errors	`SELCT * FROM users` (typo)	1064
Constraint violations	Duplicate primary key, NOT NULL violation	1062, 1048
Missing objects	Table or column doesn't exist	1146, 1054
Data type mismatch	Inserting `'abc'` into an INT column	1366
Division by zero	`SELECT 100 / 0`	1365
Deadlocks	Two transactions waiting for each other	1213
Permission denied	User lacks INSERT privilege	1142

What Happens Without Error Handling

SQL

-- This procedure has NO error handling
DELIMITER //
CREATE PROCEDURE transfer_money(
    IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2)
)
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    -- If this fails, the money is already deducted! 💥
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END //
DELIMITER ;

If the second UPDATE fails (wrong to_id, constraint violation, etc.), the money is subtracted from the sender but never added to the receiver. This is why you need error handling combined with transactions.

Key Takeaways

SQL errors stop statement execution — they don't fix themselves
Without error handling, failures can leave data in an inconsistent state
Common errors: constraint violations, missing objects, type mismatches, deadlocks
Always combine error handling with transactions for critical operations

2 TRY-CATCH Blocks

Top

The idea is simple: try to execute your SQL, and if an error occurs, catch it and handle it gracefully — roll back the transaction, log the error, or return a meaningful message.

SQL Server: TRY...CATCH

SQL Server has the cleanest TRY-CATCH syntax, very similar to C# or Java:

T-SQL (SQL Server)

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;

    COMMIT;
    PRINT 'Transfer successful';
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Error: ' + ERROR_MESSAGE();
    PRINT 'Number: ' + CAST(ERROR_NUMBER() AS VARCHAR);
END CATCH;

SQL Server Error Functions

Function	Returns	Example Value
`ERROR_NUMBER()`	Error code	`2627`
`ERROR_MESSAGE()`	Error description	`'Violation of PRIMARY KEY constraint'`
`ERROR_SEVERITY()`	Severity level (0-25)	`14`
`ERROR_STATE()`	Error state	`1`
`ERROR_LINE()`	Line number where error occurred	`4`
`ERROR_PROCEDURE()`	Procedure name (if in one)	`'transfer_money'`

MySQL: DECLARE HANDLER

MySQL doesn't have TRY-CATCH. Instead, you use DECLARE ... HANDLER inside stored procedures:

MySQL

DELIMITER //
CREATE PROCEDURE safe_transfer(
    IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transfer failed — rolled back' AS result;
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    COMMIT;
    SELECT 'Transfer successful' AS result;
END //
DELIMITER ;

MySQL Handler Types

Handler	Behavior	Use When
`EXIT HANDLER`	Executes handler block, then exits the procedure	Most common — stop on error
`CONTINUE HANDLER`	Executes handler block, then continues execution	Logging errors but proceeding

PostgreSQL: BEGIN...EXCEPTION

PostgreSQL

DO $$
BEGIN
    INSERT INTO users (id, name) VALUES (1, 'Sara');
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'User ID 1 already exists, skipping';
    WHEN OTHERS THEN
        RAISE NOTICE 'Unexpected error: %', SQLERRM;
END $$;

Pattern to remember: Regardless of the DBMS, the pattern is always the same: (1) Start a transaction, (2) Try your operations, (3) If error → ROLLBACK + log, (4) If success → COMMIT. The syntax differs, but the logic is identical.

Key Takeaways

SQL Server uses BEGIN TRY...END TRY / BEGIN CATCH...END CATCH
MySQL uses DECLARE EXIT HANDLER FOR SQLEXCEPTION inside procedures
PostgreSQL uses BEGIN...EXCEPTION WHEN...THEN
Always ROLLBACK inside the error handler to prevent partial changes
Log errors for debugging — don't just swallow them silently

3 RAISERROR vs THROW

Top

Sometimes you need to generate your own errors — when a business rule is violated (insufficient funds, invalid status transition, expired coupon). SQL Server provides two ways: the older RAISERROR and the newer THROW.

THROW (SQL Server 2012+, Recommended)

T-SQL

-- Raise a custom error
THROW 50001, 'Insufficient funds for this transfer', 1;

-- Re-throw the current error inside a CATCH block
BEGIN CATCH
    ROLLBACK;
    THROW;  -- re-throws the original error
END CATCH;

RAISERROR (Legacy, Still Used)

T-SQL

-- Basic RAISERROR
RAISERROR('Account %d has insufficient funds', 16, 1, @account_id);
--        message                              severity state  params

-- Using a message number (pre-defined)
RAISERROR(50001, 16, 1);

THROW vs RAISERROR Comparison

Feature	THROW	RAISERROR
Introduced	SQL Server 2012	SQL Server 2000
Severity	Always 16 (user error)	Configurable (0-25)
Re-throw	`THROW;` (no parameters)	Not supported
Terminates batch	Yes (always)	Only if severity >= 11
String formatting	No (plain string)	Yes (`%d`, `%s` like printf)
Recommendation	Use this for new code	Legacy — but still works

Practical Example: Business Rule Validation

T-SQL

CREATE PROCEDURE withdraw_funds(
    @account_id INT,
    @amount DECIMAL(10,2)
)
AS
BEGIN
    DECLARE @current_balance DECIMAL(10,2);

    SELECT @current_balance = balance
    FROM accounts WHERE id = @account_id;

    IF @current_balance IS NULL
        THROW 50001, 'Account not found', 1;

    IF @current_balance < @amount
        THROW 50002, 'Insufficient funds', 1;

    BEGIN TRY
        BEGIN TRANSACTION;
        UPDATE accounts SET balance = balance - @amount
        WHERE id = @account_id;
        COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH;
END;

MySQL Equivalent: SIGNAL

MySQL uses SIGNAL to raise custom errors (similar to THROW):

MySQL

DELIMITER //
CREATE PROCEDURE safe_withdraw(
    IN acct_id INT, IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE current_bal DECIMAL(10,2);
    SELECT balance INTO current_bal FROM accounts WHERE id = acct_id;

    IF current_bal < amount THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE id = acct_id;
END //
DELIMITER ;

Best practices: (1) Use error numbers 50000+ for custom errors to avoid conflicts with system errors. (2) Always include a meaningful message. (3) Use THROW for new SQL Server code. (4) In MySQL, use SIGNAL SQLSTATE '45000' for user-defined errors. (5) Always log errors to a table for debugging.

Key Takeaways

THROW is the modern way to raise errors in SQL Server (2012+)
RAISERROR is the legacy approach — still works but less clean
MySQL uses SIGNAL SQLSTATE '45000' for custom errors
Use custom errors for business rules: insufficient funds, invalid input, access denied
Use error numbers 50000+ for custom errors to avoid system conflicts

What's next? Now that you can handle errors, the next chapter takes your querying skills further with Advanced SQL Queries — subqueries, UNION/INTERSECT/EXCEPT, advanced JOINs, and Common Table Expressions (CTEs).

All Chapters in SQL Intermediate Course

Continue Learning

Previous ChapterTransactions & Concurrency Next Chapter Advanced SQL Queries

Exception Handling in SQL

In This Chapter

1 Introduction to SQL Errors

Common Error Categories

What Happens Without Error Handling

Key Takeaways

2 TRY-CATCH Blocks

SQL Server: TRY...CATCH

SQL Server Error Functions

MySQL: DECLARE HANDLER

MySQL Handler Types

PostgreSQL: BEGIN...EXCEPTION

Key Takeaways

3 RAISERROR vs THROW

THROW (SQL Server 2012+, Recommended)

RAISERROR (Legacy, Still Used)

THROW vs RAISERROR Comparison

Practical Example: Business Rule Validation

MySQL Equivalent: SIGNAL

Key Takeaways

All Chapters in SQL Intermediate Course

Continue Learning

Courses

Services

Support

Connect