The c_proc.sql command file

The following command file creates an SPL routine. It reads the full name and address of a customer and takes a last name as its only argument.

This routine shows the legacy use of CREATE PROCEDURE.

To conform with the SQL standard preferred with HCL Informix®, define a function if you want to return values from a routine.

CREATE PROCEDURE read_address (lastname CHAR(15))
   RETURNING CHAR(15), CHAR(15), CHAR(20), CHAR(15), CHAR(2), CHAR(5);
   DEFINE p_fname, p_city CHAR(15);
   DEFINE p_add CHAR(20);
   DEFINE p_state CHAR(2);
   DEFINE p_zip CHAR(5); 
   SELECT fname, address1, city, state, zipcode
      INTO p_fname,  p_add, p_city, p_state, p_zip
      FROM customer
      WHERE lname = lastname;

   RETURN p_fname, lastname, p_add, p_city, p_state, p_zip;

END PROCEDURE;