# Exceptions and Exception Handling This topic describes exceptions and exception handling in SingleStore Procedural SQL (PSQL) user-defined scalar-valued functions (UDFs) and stored procedures (SPs). ## Exception Types UDFs and SPs raise exceptions when errors occur during execution. Unhandled exceptions generate an exception message and a stack trace that is returned to the client. Two kinds of exceptions can be raised: * An internal exception, which is a predefined exception generated by SingleStore * A user-defined exception, which is defined by the user in a UDF or SP The following sections describe each kind of exception and how to handle exceptions in your UDF or SP. ## Exception Handling Syntax If you want to catch an exception, you can use a [Statement Block](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/control-flow-statements.md) containing an exception handling section beginning with the `EXCEPTION` keyword. Here is the syntax for statement blocks that can use `EXCEPTION`: ``` statement_block: [ DECLARE variable_list ] [ ...n ] BEGIN statement_list [ EXCEPTION when_clause_list ] END; when_clause_list: when_clause [ ...n ] when_clause: WHEN exception_type THEN statement_list ``` **exception\_type** An `exception_type` is the name of a built-in server error type. There is currently no published list of all the different error names, but the error name always appears in the call stack displayed for an uncaught exception. The keyword `OTHERS` can also be used as an `exception_type`. `OTHERS` matches all exceptions. **statement\_list** A list of one or more statements terminated by semicolons. ## Internal Exceptions Internal exceptions are defined by SingleStore, and they are raised when a function or procedure errors under a wide variety of conditions. These exception types encompass SQL syntax errors, insertion errors, connection errors, and so on. Here are some possible internal exceptions: | Exception Name | Description | Scenario When Exception is Thrown | | -------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------- | | `ER_SCALAR_BUILTIN_NO_ROWS_CODE` | The[Scalar](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/scalar.md)function did not return any value. | This exception is thrown when a`SCALAR`function does not return any row (value). | | `ER_BAD_FIELD_ERROR` | Column name does not exist. | A column that does not exist in a table is referenced in a query. | | `ER_TABLE_EXISTS_ERROR` | Table already exists. | While creating a table, a table with the same name already exists. | | `ER_BAD_TABLE_ERROR` | Table does not exist. | A table that does not exist is referenced in a query. | | `ER_NO_SUCH_TABLE` | Table does not exist. | A table that does not exist is referenced in a query. | | `ER_DUP_ENTRY_WITH_KEY_NAME` | Duplicate key error. The error message contains the key name as well. | A duplicate value is entered for a column with a key constraint. | | `ER_WRONG_VALUE_COUNT_ON_ROW` | Column count does not match value count. | The number of columns in a table and the number of values specified in the query are not the same. For example,`INSERT`query. | | `ER_PARSE_ERROR` | Issue with parsing dynamic SQL. | There is an error in the SQL statement. | | `ER_FILE_NOT_FOUND` | Cannot find file, usually in`LOAD DATA`statements. | Either the file does not exist or the file path is incorrect. | Consider the following example, where table `t` contains two entries and `id` is defined as a `PRIMARY KEY`: ```sql CREATE TABLE t(id INT PRIMARY KEY, str VARCHAR(255)); CREATE TABLE t_errors(dt DATETIME, id INT, str VARCHAR(255)); INSERT t VALUES (0, "foo"), (1, "bar"); ``` The following procedure attempts to insert a row with a duplicate primary key, which raises the `ER_DUP_ENTRY` exception. This exception triggers the procedure’s exception-handling behavior, where the row with a duplicate primary key is written to a `t_errors` table instead: ```sql DELIMITER // CREATE OR REPLACE PROCEDURE insert_handle_exception( id INT, str VARCHAR(255)) AS BEGIN START TRANSACTION; INSERT INTO t VALUES (1, str); COMMIT; EXCEPTION WHEN ER_DUP_ENTRY THEN ROLLBACK; START TRANSACTION; INSERT INTO t_errors VALUES (current_timestamp(), id, str); COMMIT; END // DELIMITER ; ``` When `insert_handle_exception()` is called, the error-handling behavior successfully inserts the contents of the row, and the current time, into the `t_errors` table instead: ```sql CALL insert_handle_exception(1, "baz"); ``` ```output Query OK, 0 rows affected (0.26 sec) ``` ```sql SELECT * FROM t_errors; ``` ```output +---------------------+------+------+ | dt | id | str | +---------------------+------+------+ | 2017-09-26 22:33:22 | 1 | baz | +---------------------+------+------+ ``` ```sql SELECT * FROM t; ``` ```output +----+------+ | id | str | +----+------+ | 0 | foo | | 1 | bar | +----+------+ ``` The use of `START TRANSACTION`, `COMMIT`, and `ROLLBACK` above is not really necessary in this simple example, but it does illustrate how you can use transaction control statements in conjunction with exception handling. For example, it may be necessary to roll back a transaction when you catch an exception, to make sure partial work is not committed. ## User-Generated Exceptions Users can generate their own exceptions through the `RAISE USER_EXCEPTION()` statement. `USER_EXCEPTION()` is a function that takes a string as an argument. The `RAISE` statement raises an `ER_USER_RAISE` exception. The `RAISE` keyword by itself can be used inside an `EXCEPTION` block to re-raise the currently caught exception. When in an `EXCEPTION` block, the `EXCEPTION_MESSAGE()` function can be used to obtain the message that was passed into `USER_EXCEPTION` for the currently caught exception. An example that shows a possible use of `EXCEPTION_MESSAGE()` follows. In the following example, the `attempt()` procedure raises three possible exceptions: * A user exception with message "rollback" (when i>=7). * A user exception with message "retry" (when 3\= 7 THEN RAISE user_exception("rollback"); -- line 6 ELSIF i <= 3 THEN -- line 7 j = NULL; -- line 8 force an exception by assigning NULL to a NOT NULL type END IF; RAISE user_exception("retry"); -- line 10 END // DELIMITER ; ``` ```sql DELIMITER // CREATE OR REPLACE PROCEDURE attemptWithRetry() AS DECLARE i INT; BEGIN WHILE true LOOP BEGIN START TRANSACTION; CALL attempt(); -- line 7 EXCEPTION WHEN ER_USER_RAISE THEN -- line 10 ROLLBACK; -- line 11 IF REGEXP_INSTR(EXCEPTION_MESSAGE(), "retry") THEN i = sleep(1); CONTINUE; ELSIF REGEXP_INSTR(EXCEPTION_MESSAGE(), "rollback") THEN RAISE; END IF; WHEN OTHERS THEN ROLLBACK; -- line 19 RAISE user_exception("other exception"); -- line 20 END; COMMIT; EXIT; END LOOP; END // DELIMITER ; ``` The `attemptWithRetry()` procedure illustrates the use of `OTHERS` to catch exceptions not explicitly named in a `WHEN` clause. When i <= 3 (about 40% of the time) in `attempt()`, the assignment `j = NULL` is tried; this assignment always fails because `j` is declared as `NOT NULL`. This failure generates a system exception, which is caught with `WHEN OTHERS`. When `attemptWithRetry()` is called, the expected results are: * A user exception with the message "rollback". * A user exception with the message "other exception". * Either of these exceptions may display a call stack showing multiple retries. Call `attemptWithRetry()` multiple times to see the different results. In the following call of `attemptWithRetry()`, `attempt()` tries the invalid `NULL` assignment and a system failure is generated which is caught with `OTHERS`. A user exception with the message "other exception" is raised and returned to the client. ```sql CALL attemptwithretry(); ``` ```output ERROR 2242 (HY000): Unhandled exception Type: ER_USER_RAISE (2242) Message: other exception Callstack: #0 Line 8 in `db`.`attempt` called from #1 Line 20 in `db`.`attemptWithRetry` error re-raised from #2 Line 7 in `db`.`attemptWithRetry` ``` *** Modified at: March 6, 2026 Source: [/db/v9.1/reference/sql-reference/procedural-sql-reference/exceptions-and-exception-handling/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/exceptions-and-exception-handling/) (An index of the documentation is available at /llms.txt)