Informix Guide to SQL: Syntax Segments

Use the Argument segment to pass a specific value to a routine parameter. Use the Argument segment wherever you see a reference to an argument in a syntax diagram.

A parameter list for a user-defined routine (UDR) is defined in the CREATE PROCEDURE or CREATE FUNCTION statement. If the UDR has a parameter list, you can enter arguments when you execute the UDR. An argument is a specific data element that matches the data type of one of the parameters for the UDR.

When you execute a UDR, you can enter arguments in one of two ways:

• With a parameter name (in the form parameter name = expression), even if the arguments are not in the same order as the parameters
• With no parameter name, if the arguments are in the same order as the parameters

If you use a parameter name for one argument, you must use a parameter name for all the arguments.

In the following example, both statements are valid for a user-defined procedure that expects three character arguments, t, d, and n:

EXECUTE PROCEDURE add_col (t ='customer', d ='integer', n 
='newint');

EXECUTE PROCEDURE add_col ('customer','newint','integer') ;

When you create or register a UDR with CREATE PROCEDURE or CREATE FUNCTION, you specify a parameter list with the names and data types of the parameters the UDR expects.

If you attempt to execute a UDR with more arguments than the UDR expects, you receive an error.

If you execute a UDR with fewer arguments than the UDR expects, the arguments are said to be missing. The database server initializes missing arguments to their corresponding default values. This initialization occurs before the first executable statement in the body of the UDR.

If missing arguments do not have default values, the database server initializes the arguments to the value UNDEFINED. However, you cannot use a variable with a value of UNDEFINED within the UDR. If you do, the database server issues an error.

The diagram for Argument refers to this section.

You can use any expression as an argument, except an aggregate expression. If you use a subquery or function call, the subquery or function must return a single value of the appropriate data type and size. For a complete description of syntax and usage, see Expression.