SQL SQLSTATE

What is SQLSTATE in SQL?

SQLSTATE is a five-character return code that identifies the completion status or error type of the most recently executed SQL statement.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SQLSTATE:

SQL SQLSTATE Full Explanation

SQLSTATE is part of the SQL Standard error diagnostics framework. Every SQL statement returns a five-character alphanumeric code: the first two characters form the class (broad category) and the last three form the subclass (specific condition). A value of '00000' means successful completion, while any non-zero class indicates a warning or error. SQLSTATE values are vendor-independent, making them preferable to product-specific numeric codes. Applications can read SQLSTATE through CLI/ODBC drivers, GET DIAGNOSTICS, or language-specific interfaces, and can test for particular states in condition handlers or exception blocks to implement robust error handling. Common classes include '01' (warning), '02' (no data), '22' (data exception), and '28' (invalid authorization). Not all databases implement every code defined by the standard, but compliant engines map their internal errors to the closest SQLSTATE.

SQL SQLSTATE Syntax

-- Retrieve the SQLSTATE of the last statement
GET DIAGNOSTICS :state = RETURNED_SQLSTATE;

-- Declare a handler for a specific SQLSTATE (MySQL example)
DECLARE CONTINUE HANDLER FOR SQLSTATE '23505' SET dup := TRUE;

-- Raise an error with a specific SQLSTATE (PostgreSQL example)
RAISE EXCEPTION USING ERRCODE = '23514', MESSAGE = 'Check violation';

SQL SQLSTATE Parameters

Example Queries Using SQL SQLSTATE

-- Example 1: Capture SQLSTATE after a statement (PostgreSQL)
BEGIN;
UPDATE users SET email = NULL WHERE id = 5; -- may violate NOT NULL
GET DIAGNOSTICS state = RETURNED_SQLSTATE;
COMMIT;

-- Example 2: Suppress duplicate-key errors (MySQL)
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
BEGIN
  -- logic executed when a duplicate key error occurs
END;
INSERT INTO orders(id,user_id) VALUES (1,10);

-- Example 3: Raise custom exception with SQLSTATE (PostgreSQL)
RAISE EXCEPTION USING ERRCODE = 'P0001', MESSAGE = 'Manual failure';

Expected Output Using SQL SQLSTATE

Example 1 stores a five-character code (e
g
, '23502' for NOT NULL violation) in the variable state
Example 2 continues program flow only when SQLSTATE '23000' (integrity constraint violation) occurs
Example 3 aborts the transaction and returns SQLSTATE 'P0001' to the caller

Use Cases with SQL SQLSTATE

Detecting and branching on specific errors inside stored procedures
Translating vendor-specific errors to portable logic in middleware
Logging standardized error information for observability platforms
Retrying transient errors (e.g., deadlocks) based on class '40'
Raising application-defined errors with meaningful SQLSTATE codes

Common Mistakes with SQL SQLSTATE

Confusing SQLSTATE with vendor numeric error codes (SQLCODE)
Forgetting that '00000' means success, leading to wrong error checks
Comparing the entire five characters when only the class is needed
Using non-standard strings (must be exactly five characters)
Assuming every database supports GET DIAGNOSTICS in the same syntax

First Introduced In

SQL-92

Frequently Asked Questions

What does SQLSTATE '00000' mean?

'00000' signals successful completion with no warnings.

How do I check SQLSTATE in a stored procedure?

Assign RETURNED_SQLSTATE via GET DIAGNOSTICS (PostgreSQL) or test it in a DECLARE HANDLER (MySQL) right after the statement you want to monitor.

Do all databases implement every SQLSTATE code?

No. Most databases implement the common classes but may omit less relevant subclasses. Always test on your target engine.

Is SQLSTATE better than SQLCODE?

Yes for portability. SQLSTATE is standardized, while SQLCODE values are vendor-specific.