SQL Language Guide > SQL Language Guide > SQL Statements > CALL X100 > Variable Declaration
Was this helpful?
Variable Declaration
The DECLARE statement can be used in a database procedure definition to declare a list of local variables. It is recommended to place it before the BEGIN clause of the database procedure definition while using the statement.
Nullable variables are initialized to null. Non-nullable variables are initialized to the default value according to data type. Character data types are initialized to blank, and numeric data types are initialized to zero. Any non-nullable variables declared without an explicit default value are initialized to the default value.
 
Nullable Option
Default Option
Results
WITH NULL
(none specified)
The variable can be null; default value is null.
NOT NULL
(none specified)
 
The default is 0 or blank (according to data type).
(none specified)
WITH DEFAULT
Not valid without a NULL clause.
(none specified)
NOT DEFAULT
Not valid without a NULL clause.
WITH NULL
WITH DEFAULT
Not valid.
WITH NULL
NOT DEFAULT
Not valid.
NOT NULL
WITH DEFAULT
The variable defaults to 0 or blank, according to its data type.
NOT NULL
NOT DEFAULT
The variable defaults to 0 or blank, according to its data type.
Variable declaration format
The variable declaration has the following format:
DECLARE var_name {, var_name} [=] var_type
[NOT NULL [WITH | NOT DEFAULT] | WITH NULL];
{var_name {, var_name} [=] var_type
[NOT NULL [WITH | NOT DEFAULT] | WITH NULL];}
var_name
Specifies the name of the local variable. A variable name must be unique within the procedure. It cannot match the name of any other procedure variable or parameter.
var_type
Specifies the data type of the variable. A local variable can be any data type except a SYSTEM_MAINTAINED TABLE_KEY or OBJECT_KEY.
The following example demonstrates the declaration and usage of local variables:
CREATE PROCEDURE variables (vmny MONEY NOT NULL) AS
DECLARE
vi4 INTEGER NOT NULL;
vf8 FLOAT;
vc11 CHAR(11) NOT NULL;
vdt DATE;
BEGIN
vi4 = 1234;
vf8 = null;
vc11 = '26-jun-1957';
vdt = date(:vc11);
vc11 = :vmny;--data type conversion error
vmny = :vf8;--null to non-null conversion
error
RETURN :vi4;
END;
Last modified date: 12/19/2024