DECLARE Variable

The DECLARE Variable statement lets you define a local variable within a procedure. Local variables are used only within the procedure.

Scope

Procedures only

SQL Syntax

  DECLARE { LocalVariable}[,...] VariableType {LANG = VariableLangName]
  [DEFAULT {Constant 
            NULL 
            CurrentFunction}][NOT NULL]

Parameters

LocalVariable: specifies the name of the local variable. A variable name may not be the same as a parameter name in the same procedure.
VariableType: specifies the data type of the local variable. All the ALLBASE/SQL data types are permitted except LONG data types.
VariableLangName: specifies the language of the data (for character data types only) to be stored in the local variable. This name must be either n-computer or the current language of the DBEnvironment.
DEFAULT: specifies the default value of the local variable. The default can be a constant, NULL, or a date/time current function. The data type of the default value must be compatible with the data type of the variable.
NOT NULL: means the variable cannot contain null values. If NOT NULL is specified, any statement that attempts to place a null value in the variable is rejected.

Description

Declarations must appear at the beginning of the stored procedure body, following the first BEGIN statement.
No two local variables or parameters in a procedure may have the same name.
Local variable declarations may not be preceded by labels.
If no DEFAULT clause is given for a column in the table, an implicit DEFAULT NULL is assumed. Any INSERT statement, which does not include a column for which a default has been declared, causes the default value to be inserted into that column for all rows inserted.
For a CHAR column, if the specified default value is shorter in length than the target column, it is padded with blanks. For a CHAR or VARCHAR column, if the specified default value is longer than the target column, it is truncated.
For a BINARY column, if the specified default value is shorter in length than the target column, it is padded with zeroes. For a BINARY or VARBINARY column, if the specified default value is longer than the target column, it is truncated.

Authorization

Anyone can use the DECLARE statement in a procedure.

Example

   DECLARE input, output CHAR(80);
   DECLARE nrows INTEGER;
   DECLARE PartNumber CHAR(16) NOT NULL;