Skip to content
Tutorials Center
My learning

Topics

Lesson 53 of 75 ยท Programmable Objects

Cursors, Handlers and Error Handling

Handle errors in MySQL routines: DECLARE CURSOR, CONTINUE and EXIT handlers, SIGNAL and RESIGNAL, GET DIAGNOSTICS and dynamic SQL with PREPARE and EXECUTE.

Real routines have to cope with things going wrong: a row is missing, a duplicate key appears, a business rule is broken. MySQL's stored routines have their own error machinery, separate from the exceptions you know from PHP or Java. You declare handlers that react to conditions, you raise errors yourself with SIGNAL, and you can inspect an error with GET DIAGNOSTICS.

This lesson also covers cursors, which let a routine walk through a query result one row at a time, and dynamic SQL, which builds and runs a statement from a string. Both are tools of last resort, and you will see why. All of this is a MySQL server feature that needs stored routines, so the routine code is read-only here. The live examples use the SQLite stand-in for the set-based alternative to a cursor and for the idea behind SIGNAL. Read Stored Procedures and Functions first if DELIMITER and BEGIN ... END are new.

The error toolkit

HANDLER Catch a condition and react.
SIGNAL Raise your own error.
GET DIAGNOSTICS Read the error details.
RESIGNAL Pass the error up after cleanup.
1 / 2

Conditions and handlers

A condition is something that happens while a statement runs: an error with a number (like 1062, duplicate key) and a five-character SQLSTATE (like 23000), a warning, or "no more rows". A handler says what to do when one occurs:

SQL
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_missing = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ROLLBACK;
END;

All routine code in this lesson is read-only here, because it needs a real server.

  • A CONTINUE handler runs its action and then the routine resumes after the statement that caused the condition.
  • An EXIT handler runs its action and then leaves the BEGIN ... END block it was declared in.

What the handler can listen for:

Condition Meaning
SQLEXCEPTION Any error (SQLSTATE class other than 00, 01, 02)
SQLWARNING Any warning (SQLSTATE class 01)
NOT FOUND No rows (SQLSTATE class 02), for example at the end of a cursor
SQLSTATE '23000' A specific SQLSTATE value
1062 A specific MySQL error number (duplicate entry)

A complete example: insert a customer, and if the email already exists, return a friendly status instead of an error:

SQL
DELIMITER //

CREATE PROCEDURE add_customer(IN p_email VARCHAR(120), OUT p_status VARCHAR(20))
BEGIN
  DECLARE EXIT HANDLER FOR 1062      -- duplicate key
  BEGIN
    SET p_status = 'duplicate';
  END;

  INSERT INTO customers (email) VALUES (p_email);
  SET p_status = 'created';
END //

DELIMITER ;

CALL add_customer('[email protected]', @s);
SELECT @s;

Two rules matter. First, declaration order is fixed: variables and conditions first, then cursors, then handlers. Second, a handler without a matching action silently swallows the error, so a careless DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; hides every failure, including ones you never expected. Prefer a specific handler (a number or SQLSTATE) over the catch-all, and make a catch-all roll back and re-raise.

Raising errors: SIGNAL and RESIGNAL

SIGNAL raises an error from your own code, which is how a routine enforces a business rule:

SQL
DELIMITER //

CREATE PROCEDURE withdraw(IN p_account INT, IN p_amount DECIMAL(10,2))
BEGIN
  DECLARE v_balance DECIMAL(10,2);

  SELECT balance INTO v_balance FROM accounts WHERE id = p_account FOR UPDATE;

  IF v_balance < p_amount THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Insufficient funds', MYSQL_ERRNO = 5001;
  END IF;

  UPDATE accounts SET balance = balance - p_amount WHERE id = p_account;
END //

DELIMITER ;

SQLSTATE values starting with 45 are reserved for user-defined conditions, so 45000 is the conventional choice. MESSAGE_TEXT is what the caller sees (up to 128 characters) and MYSQL_ERRNO lets your application tell different errors apart without parsing text. The client gets an ordinary error:

TEXT
ERROR 5001 (45000): Insufficient funds

RESIGNAL is SIGNAL for use inside a handler: it re-raises the error that triggered the handler, optionally changing its message. It is the usual way to do cleanup and then still fail:

SQL
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ROLLBACK;
  RESIGNAL;          -- the caller still sees the original error
END;

GET DIAGNOSTICS

Inside a handler you often want to know what went wrong. GET DIAGNOSTICS copies details of the current condition into variables:

SQL
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  GET DIAGNOSTICS CONDITION 1
    @sqlstate = RETURNED_SQLSTATE,
    @errno    = MYSQL_ERRNO,
    @text     = MESSAGE_TEXT;
  ROLLBACK;
  INSERT INTO error_log (sqlstate, errno, message, logged_at)
  VALUES (@sqlstate, @errno, @text, NOW());
  RESIGNAL;
END;

The order matters. If the INSERT into the log came before the ROLLBACK, the rollback would erase the log row too, because it belongs to the same transaction. Roll back first, then write the log row (it is then its own statement), or log from the application. This catches many people. Plain GET DIAGNOSTICS (without CONDITION) reads statement-level information such as ROW_COUNT. Inside a handler, GET STACKED DIAGNOSTICS reads the condition that triggered the handler, while GET CURRENT DIAGNOSTICS reads the handler's own statements.

Cursors

A cursor lets a routine read a query's rows one at a time. The pattern has four parts: declare, open, fetch in a loop, close.

SQL
DELIMITER //

CREATE PROCEDURE apply_loyalty_discounts()
BEGIN
  DECLARE v_done INT DEFAULT 0;
  DECLARE v_id INT;
  DECLARE v_spent DECIMAL(12,2);

  DECLARE cur CURSOR FOR
    SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id, v_spent;
    IF v_done = 1 THEN
      LEAVE read_loop;
    END IF;

    IF v_spent > 1000 THEN
      UPDATE customers SET discount = 10 WHERE id = v_id;
    END IF;
  END LOOP;
  CLOSE cur;
END //

DELIMITER ;

The NOT FOUND handler is how you detect the end of the cursor: FETCH past the last row raises that condition, and the handler sets v_done. MySQL cursors are read-only, non-scrollable (forward only) and asensitive (you should not assume whether they see changes made while open).

Cursors are slow compared to set-based SQL because they process one row per loop turn with all the interpretive overhead. The routine above does one UPDATE per customer, while a single statement does the same job for all customers at once. The live stand-in shows that rewrite:

SQL Replace a cursor loop with one UPDATE

Press Run (or Ctrl + Enter) to see the result here.

Ada (1,150 in total) and Cy (1,500) get the discount and Bo (200) does not. Use a cursor only when each row needs something a single statement cannot express, such as calling another procedure per row, and even then consider whether the application loop would be clearer.

An analogy you can run: raising an error from SQL

SQLite has no routines, but it has triggers, which can abort a statement with a custom message through RAISE. That is the closest live relative of SIGNAL: a rule is checked inside the database, and a violation stops the statement with your own text. This example ends on purpose with the failing UPDATE, which is the point of the demo, so the last statement reports an error:

SQL A custom error, like SIGNAL (ends with an error on purpose)

Press Run (or Ctrl + Enter) to see the result here.

The first update succeeds and leaves 50. The second one fails with the message Insufficient funds and changes nothing. The triggers lesson shows the MySQL way to do the same thing with SIGNAL.

Dynamic SQL: PREPARE, EXECUTE, DEALLOCATE

Sometimes the SQL text itself has to be built at run time, for example when a table name comes from a variable. Identifiers cannot be bound as parameters, so you build a string:

SQL
SET @sql = CONCAT('SELECT COUNT(*) INTO @n FROM `', 'orders_2026', '`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT @n;

Values, in contrast, should always be bound with ? and USING:

SQL
SET @min_total = 100;
PREPARE stmt FROM 'SELECT id, total FROM orders WHERE total >= ?';
EXECUTE stmt USING @min_total;
DEALLOCATE PREPARE stmt;

Two restrictions: PREPARE ... FROM takes a string literal or a user variable (@sql), not a local DECLAREd variable, and USING also takes user variables only. And the danger is real: building SQL by concatenating text that came from a user is SQL injection. If you must pass an identifier, validate it against a whitelist (for example by checking information_schema.TABLES) and quote it with backticks. Never concatenate values.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the difference between a CONTINUE and an EXIT handler?

    After a CONTINUE handler's action, execution carries on with the next statement. After an EXIT handler's action, the block it was declared in ends.
  2. 2. Which SQLSTATE class is reserved for user-defined conditions raised with SIGNAL?

    SQLSTATE values beginning with 45 are for user-defined conditions, and 45000 is the conventional 'unhandled user-defined exception'.
  3. 3. How does a cursor loop usually detect that there are no more rows?

    Fetching past the last row raises the NOT FOUND condition. The handler sets a variable that the loop tests.
  4. 4. Why is a catch-all handler such as CONTINUE HANDLER FOR SQLEXCEPTION with an empty body dangerous?

    An empty handler swallows the error and the routine carries on as if nothing happened. Use specific handlers or log and RESIGNAL.
  5. 5. Which values can be bound as ? parameters in a prepared statement?

    Placeholders stand for values, not identifiers or SQL fragments. Table or column names must be validated and quoted when building the string.

Summary

  • Handlers (DECLARE CONTINUE|EXIT HANDLER FOR condition) react to errors, warnings and NOT FOUND. Declare variables first, then cursors, then handlers.
  • SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = ... raises your own error. RESIGNAL re-raises inside a handler.
  • GET DIAGNOSTICS reads the error details. Do not log to a table inside the transaction you are about to roll back.
  • Cursors (DECLARE, OPEN, FETCH, CLOSE) walk rows one at a time, read-only and forward-only, and are usually replaceable by a set-based statement.
  • PREPARE, EXECUTE and DEALLOCATE PREPARE run dynamic SQL. Bind values with ? and validate any identifier you concatenate.

Next: Triggers, where the database reacts to changes by itself.