CREATE PROCEDURE FROM

Use the CREATE PROCEDURE FROM statement to access a user-defined procedure. The actual text of the CREATE PROCEDURE statement resides in a separate file.

This statement is an extension to the ANSI/ISO standard for SQL. You can use this statement with ESQL/C.

In Extended Parallel Server, which does not support the CREATE FUNCTION FROM statement, you can use the CREATE PROCEDURE FROM statement to create a SPL routine from a file, whether or not the SPL routine returns a value.

Syntax

>>-CREATE PROCEDURE FROM--+-'file'---+-------------------------><
                          '-file_var-'

Element	Description	Restrictions	Syntax
file	Pathname and filename of file that contains full text of a CREATE PROCEDURE statement. Default pathname is the current directory.	Must exist, and can contain only one CREATE PROCEDURE statement. See also Default Directory That Holds the File.	Operating-system specific
file_var	Name of a program variable that contains file specification	Must be of a character data type; its contents have same restrictions as file	Language specific

Usage

You cannot create a user-defined procedure directly in an ESQL/C program. That is, the program cannot contain the CREATE PROCEDURE statement.

To use a user-defined procedure in an ESQL/C program

Create a source file with the CREATE PROCEDURE statement.
Use the CREATE PROCEDURE FROM statement to send the contents of this source file to the database server for execution.
The file can contain only one CREATE PROCEDURE statement.

For example, suppose that the following CREATE PROCEDURE statement is in a separate file, called raise_pr.sql:

CREATE PROCEDURE raise_prices( per_cent int )
   UPDATE stock -- increase by percentage;
   SET unit_price = unit_price + 
      ( unit_price * (per_cent / 100) );
END PROCEDURE;

In the ESQL/C program, you can access the raise_prices( ) SPL procedure with the following CREATE PROCEDURE FROM statement:

EXEC SQL create procedure from 'raise_pr.sql';

In Dynamic Server, if you are not sure whether the UDR in the file returns a value, use the CREATE ROUTINE FROM statement.

When the IFX_EXTEND_ROLE configuration parameter is set to ON, only users who have the built-in EXTEND role can create external routines.

User-defined procedures, like user-defined functions, use the collating order that was in effect when they were created. See SET COLLATION for information about using non-default collation.