Example of the SIGNAL statement in an SQL procedure

You can use the SIGNAL statement anywhere within an SQL procedure to raise a particular condition.

The following example uses an ORDERS table and a CUSTOMERS table that are defined in the following way:

CREATE TABLE ORDERS
  (ORDERNO     INTEGER NOT NULL,
   PARTNO      INTEGER NOT NULL,
   ORDER_DATE  DATE DEFAULT,
   CUSTNO      INTEGER NOT NULL,
   QUANTITY    SMALLINT NOT NULL,
   CONSTRAINT REF_CUSTNO FOREIGN KEY (CUSTNO)
     REFERENCES CUSTOMERS (CUSTNO) ON DELETE RESTRICT,
   PRIMARY KEY (ORDERNO,PARTNO));

CREATE TABLE CUSTOMERS
  (CUSTNO      INTEGER NOT NULL,
   CUSTNAME    VARCHAR(30),
   CUSTADDR    VARCHAR(80),
   PRIMARY KEY (CUSTNO));

Example: Using SIGNAL to set message text

Suppose that you have an SQL procedure for an order system that signals an application error when a customer number is not known to the application. The ORDERS table has a foreign key to the CUSTOMERS table, which requires that the CUSTNO exist in the CUSTOMERS table before an order can be inserted:

CREATE PROCEDURE submit_order
  (IN ONUM INTEGER, IN PNUM INTEGER,
   IN CNUM INTEGER, IN QNUM INTEGER)
  LANGUAGE SQL
  MODIFIES SQL DATA
  BEGIN
    DECLARE EXIT HANDLER FOR SQLSTATE VALUE '23503'
      SIGNAL SQLSTATE '75002'
        SET MESSAGE_TEXT = 'Customer number is not known';
    INSERT INTO ORDERS (ORDERNO, PARTNO, CUSTNO, QUANTITY)
                VALUES (ONUM, PNUM, CNUM, QNUM);
  END

In this example, the SIGNAL statement is in the handler. However, you can use the SIGNAL statement to invoke a handler when a condition occurs that will result in an error.