602SQL Documentation Index  
<­Routine Description UpRoutines Stored on the Server Modifying Routines - ALTER PROCEDURE and ALTER FUNCTION­>

Creating Routines - CREATE PROCEDURE and CREATE FUNCTION 

statement_CREATE_routine ::= CREATE [ IF NOT EXISTS ] routine_description;

This statement creates a new routine stored on the server according to the routine_description definition.

The maximum length of a routine name is 31 characters. Each routine must have a unique name in the schema context. If there already exists a routine of the specified name the error sqlstate 40004 (KEY_DUPLICITY) occurs.

You can also create a routine from the Control Panel of the 602SQL Client.

Example:

Function that returns a number that is equal to the highest number +1.

CREATE FUNCTION maxnumber () RETURNS INT;  
  BEGIN  DECLARE maxvalue INT;  
  SELECT MAX(number)+1 INTO maxvalue FROM Tab1;  
  RETURN (maxvalue);  
END

Procedure that counts records in a table that meet the specified condition.

CREATE PROCEDURE RecCount (IN number INT, OUT res INT);
BEGIN
  SELECT COUNT(*) INTO res
  FROM Tab2
  WHERE numberint = number;
END

See

<­Routine Description UpRoutines Stored on the Server Modifying Routines - ALTER PROCEDURE and ALTER FUNCTION­>