DECLARE Variable

Scope

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

Example

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