SQL Language Guide : 7. SQL Statements : CREATE PROCEDURE : Nullability and Default Values for Parameters
 
Share this page                  
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 combined effects of these clauses are as follows:
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.