References to SQL parameters and SQL variables

Host variables cannot be specified in SQL functions, SQL procedures, SQL triggers, or compound (dynamic) statements. SQL parameters can be referenced anywhere in the routine and can be qualified with the routine name. SQL variables can be referenced anywhere in the compound statement in which they are declared, including any statement that is directly or indirectly nested within that compound statement. If the compound statement where the variable is declared has a label, references to the variable name can be qualified with that label

All SQL parameters and SQL variables are considered nullable except SQL variables that are explicitly declared as NOT NULL. The name of an SQL parameter or SQL variable in an SQL routine can be the same as the name of a column in a table or view referenced in the routine. The name of an SQL variable can also be the same as the name of another SQL variable declared in the same routine. This can occur when the two SQL variables are declared in different compound-statements. The compound-statement that contains the declaration of an SQL variable determines the scope of that variable. See compound-statement, for more information.

Names that are the same should be explicitly qualified. Qualifying a name clearly indicates whether the name refers to a column, global variable, SQL variable, or SQL parameter. If the name is not qualified, or qualified but still ambiguous, the following rules describe how the name is resolved. The name is resolved by checking for a match in the following order:

• If the tables and views specified in an SQL routine body exist at the time the routine is created, the name will first be checked as a column name.
• If not found as a column, it will then be checked as an SQL variable name. The SQL variable can be declared within the compound-statement that contains the reference, or within a compound statement in which that compound statement is nested. If two SQL variables are within the same scope and have the same name,¹ the SQL variable that is declared in the innermost compound statement is used.
• If not found as an SQL variable name, the name will be checked as an SQL parameter name.

If the name is still not resolved as a column, SQL variable, or SQL parameter and the scope of the name included a table or view that does not exist at the current server, it will be assumed to be a column or global variable. If all the tables and views exist at the current server, it will be assumed to be a global variable. If the SQL_GVAR_BUILD_RULE QAQQINI option is *EXIST and the global variable does not exist, an error will be issued.

The name of an SQL variable or SQL parameter in an SQL routine can be the same as the name of an identifier used in certain SQL statements. If the name is not qualified, the following rules describe whether the name refers to the identifier or to the SQL parameter or SQL variable. Qualified SQL parameters and SQL variables are not supported for these names.

• In the SET PATH and SET SCHEMA statements, the name is checked as an SQL parameter name or SQL variable name. If not found as an SQL variable or SQL parameter name, it will then be used as an identifier.
• In the CONNECT, DISCONNECT, RELEASE, and SET CONNECTION statements, the name is used as an identifier.
• In the CALL statement, the name is used as an identifier.
• In the ASSOCIATE LOCATORS and DESCRIBE PROCEDURE statements when used in a CREATE TRIGGER statement, the name is used as an identifier.