Example

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 includes a foreign key to the CUSTOMER table, requiring that the CUSTNO exist before an order can be inserted.

   CREATE PROCEDURE SUBMIT_ORDER
      (IN ONUM INTEGER, IN CNUM INTEGER,
       IN PNUM 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, CUSTNO, PARTNO, QUANTITY)
            VALUES (ONUM, CNUM, PNUM, QNUM); 	
      END