Icon CREATE PROCEDURE

Creates a new procedure.

Syntax
CREATE PROCEDURE <Name>
([<ParamDefinition>[,ParamDefinition]])
EXTERNAL NAME <ModuleName> | <BodyDefinition>
[DESCRIPTION <Description>]
[VERSION <VersionNumber>]
[ATTRIBUTES <CustomAttributes>]

<ParamDefinition> =

<Mode> <Name> <DataType> [<Description>]

<Mode> =

IN|OUT|INOUT

<DataType> =

CHARACTER|CHAR [(<Length>]) [<CollationName>]
CHARACTER VARYING|VARCHAR [(<Length>]) [<CollationName>]
GUID
BYTE [(<LengthInBytes>])
BYTE VARYING|VARBYTE [(<LengthInBytes>])
BINARY LARGE OBJECT|BLOB
CHARACTER LARGE OBJECT|CLOB [<CollationName>]
BOOLEAN|BOOL
SMALLINT
INTEGER|INT
BIGINT
FLOAT [(<Precision>,<Scale>)]
DECIMAL|NUMERIC [(<Precision>,<Scale>)]
DATE
TIME
TIMESTAMP
INTERVAL YEAR [TO MONTH]
INTERVAL MONTH
INTERVAL DAY [TO HOUR|MINUTE|SECOND|MSECOND]
INTERVAL HOUR [TO MINUTE|SECOND|MSECOND]
INTERVAL MINUTE [TO SECOND|MSECOND]
INTERVAL SECOND [TO MSECOND]
INTERVAL MSECOND

<BodyDefinition> =

BEGIN
   [<Declaration>;]
   [<Declaration>;]
   [<Statement>;]
   [<Statement>;]
[EXCEPTION
   [<Statement>;]]
END

Usage
Use this statement to create a new procedure in a given database. Procedures can be used in jobs, other functions and procedures, and triggers.

Information If you wish to return a result set from a procedure, declare the cursor in the procedure using the WITH RETURN clause and leave the cursor open when the procedure completes.

Examples
-- The following procedure updates any Customer row
-- with a State column value of 'FL' to 'NY' and returns a cursor
-- on the Customer table.

CREATE PROCEDURE UpdateState()
BEGIN
   DECLARE CustCursor CURSOR WITH RETURN FOR Stmt;
   DECLARE State CHAR(2) DEFAULT '';

   PREPARE Stmt FROM 'SELECT * FROM Customer';

   OPEN CustCursor;

   FETCH FIRST FROM CustCursor ('State') INTO State;

   WHILE NOT EOF(CustCursor) DO
      IF (State = 'FL') THEN
         UPDATE CustCursor SET 'State' = 'NY';
      END IF;
      FETCH NEXT FROM CustCursor ('State') INTO State;
   END WHILE;
END

Required Privileges
The current user must be granted the CREATE privilege on the current database in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations
This statement deviates from the SQL 2003 standard in the following ways:

DeviationDetails
DESCRIPTIONThe DESCRIPTION clause is an ElevateDB extension.
VERSIONThe VERSION clause is an ElevateDB extension.
ATTRIBUTESThe ATTRIBUTES clause is an ElevateDB extension.
Image