602SQL Documentation Index  

Procedures and Function Stored on the Server

Procedures and functions stored on the server simplify the creation of database applications and help make application operation faster. A client application that uses stored routines only call complete routines (passing input parameters to them and receiving the output parameters), instead of sending special requests to the server for executing each statement present in the routines.

If the routine definition syntax is incorrect, a compilation error occurs.

A procedure may be executed by calling the CALL SQL statement.

A function is executed, if a call to this function is a part of a expression. The RETURN statement must be executed in the function body when the function is running. If it is not executed, an error sqlstate 2F001 (SQ_NO_RETURN_IN_FNC) occurs.

You can create, modify and delete a routine:

The routine is defined according to the language for writing routines and triggers from SQL3 in both environments. The text of an editable routine begins with the keyword PROCEDURE or FUNCTION in the 602SQL Client. The words CREATE or ALTER are omitted.

Variations from the SQL Standard

More details about stored routines can be found in the following chapters:

It may be useful for debugging purposes to trace SQL procedure execution (situation TRACE_PROCEDURE_CALL ).

Example: Using a function as an integrity constraint on a table:

The column STRING and the clause CHECK (CHECKS(STRING))) is in the table.

The routine CHECKS is in the same application, that checks whether the received string is longer than 8 characters. If not, the function returns FALSE and the integrity constraint fails:

FUNCTION `CHECKS`(IN str CHAR(12)) RETURNS BOOLEAN;
BEGIN
  DECLARE temp BOOLEAN DEFAULT FALSE;
  IF CHAR_LENGTH(str)>8 THEN 
    SET temp=TRUE; 
  END IF;
  RETURN temp;
END

List of topics: