Informix Guide to SQL: Syntax SQL Statements

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.

In Enterprise Decision Server, use this statement to access any SPL routine. Enterprise Decision Server does not support the CREATE FUNCTION FROM statement.

Use this statement with ESQL/C.

You cannot create a user-defined procedure directly in an ESQL/C program. That is, the program cannot contain the CREATE PROCEDURE statement. The following steps describe how you can use a user-defined procedure in an ESQL/C program:

1. Create a source file with the CREATE PROCEDURE statement.
2. Use the CREATE PROCEDURE FROM statement to send the contents of this source file to the database server for execution.

The file that you specify in the file parameter 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';

If you are not sure whether the UDR in the file is a user-defined function or a user-defined procedure, use the CREATE ROUTINE FROM statement.

The filename that you provide is relative.

On UNIX, if you specify a simple filename instead of a full pathname in the file parameter, the client application looks for the file in your home directory on the computer where the database resides. If you do not have a home directory on this computer, the default directory is the root directory.

On Windows NT, if you specify a filename but not a directory in the file parameter, the client application looks for the file in your current working directory if the database is on the local computer. Otherwise, the default directory is %INFORMIXDIR%\bin.

Important: The ESQL/C preprocessor does not process the contents of the file that you specify. It just sends the contents to the database server for execution. Therefore, there is no syntactic check that the file that you specify in CREATE PROCEDURE FROM actually contains a CREATE PROCEDURE statement. However, to improve readability of the code, Informix recommends that you match these two statements.

Related Information

Related statements: CREATE PROCEDURE, CREATE FUNCTION FROM, and CREATE ROUTINE FROM