Nullability and Default Values for Parameters

Database procedures can be called from embedded SQL applications or from interactive SQL. The caller supplies values for procedure parameters. The with default, not default, with null, and not null clauses can be used to specify whether parameters have default values and whether they are nullable. These clauses have the following meanings for database procedure parameters:

with default - The caller does not have to specify a value for the parameter. If the parameter is nullable, its default value is null. If the parameter is not nullable, its default value is 0 (for numeric data types) or blanks (for character data types).
not default - The caller must specify a value for the parameter. If no value is specified, the DBMS Server issues an error.
with null - The parameter can be null.
not null - The parameter cannot be null.

The following table lists the combined effects of these clauses:

Parameter	Description
with null	The parameter can be null. If no value is provided, the DBMS Server passes a null.
not null with default	The parameter does not accept nulls. If no value is provided, the DBMS Server passes 0 for numeric and money columns, or an empty string for character and date columns.
not null not default or not null	The parameter is mandatory and does not accept nulls.
with null with default	Not allowed.
with null not default	Not allowed.
with default	Not allowed without not null clause.
not default	Not allowed without not null clause.

Set Of Parameters

A SET OF parameter is required either when a global temporary table is being passed to the procedure or when the procedure is invoked by the triggering of a statement level rule. Also, a SET OF parameter declaration consists of a SET OF parameter name and an accompanying elements list. For more information, see Create Rule.

In the case of a procedure invoked by an execute procedure statement with a global temporary table parameter, the SET OF elements correspond to the temporary table columns. For more information, see Temporary Table Parameter under Execute Procedure.

In the case of a procedure invoked by a statement level rule, the SET OF element list consists of one entry for each actual parameter in the create rule execute procedure clause. The syntax of these entries is identical to that of normal (that is, non-SET OF) formal parameters. The type definitions must be compatible with (though not necessarily identical to) the corresponding actual parameters. The names must be the same, however, as this is how the equivalence between the actual parameters and the SET OF elements is determined.

Once a SET OF parameter is defined in a create procedure statement, it can be treated exactly like any base table or view from within the procedure. The SET OF elements are the columns of the table and the parameter name is the surrogate name of the table. The parameter name can be used as a table name in any select, delete, update, or insert statement within the procedure.

For example, it can be used in an "insert...select..." statement to return the multi-row result of a complex select statement with a single procedure call, or it can be used in the from clause of an update to effect the update of many rows with a single procedure call.

For example:

create procedure gttproc (gtt1 set of (coll int, col2 float not null, col3 char(8))) as begin
....
insert into table select * from gtt1;
....
end;

gtt1 is defined as a "set of" parameter to procedure gttproc and is used in the from clause of a select statement in the body of the procedure.