References to SQL parameters and variables in SQL PL

Variables can be referenced in SQL statements in SQL functions, SQL procedures, and triggers. However, host variables cannot be specified in these objects. Instead, depending on the context, SQL variables, SQL parameters, transition variables, and global variables can be referenced anywhere in an SQL statement where an expression or variable can be specified in SQL functions, SQL procedures, and triggers. SQL variables can be referenced anywhere in the compound statement in which they are declared, including any SQL statement that is directly or indirectly nested within that compound statement. SQL parameters can be referenced in an SQL function or SQL procedure body. Transition variables can be referenced in a trigger body.

Ambiguity can arise in an SQL statement with variables, particularly when two or more variables have the same name or when a variable and another object have the same name. In such situations, it might be confusing to determine which variable is referenced and whether the identifier is the name of a variable or another object, such as a column. If the identifier is the name of a variable, it might be difficult to determine whether the reference is intended for an SQL variable, SQL parameter, transition variable, or global variable.

The name of an SQL variable, SQL parameter, or global variable in an SQL function or SQL procedure can be the same as the name of a column in a table or view referenced in the function or procedure. The name of an SQL variable, transition variable, or global variable in a trigger can be the same as the name of a column in a table or view that is referenced in the trigger. The name of an SQL variable can also be the same as the name of another SQL variable declared in the same function, procedure, or trigger. 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 additional information.

Names that are the same should be explicitly qualified. Qualifying a name can clarify whether the name refers to a column, global variable, SQL variable, SQL parameter, or transition variable:

• An SQL parameter can be qualified with the name of the SQL function or SQL procedure.
• An SQL variable can be qualified with the label of the compound statement where an SQL variable is declared.
• A transition variable can be qualified with the correlation name specified in the CREATE TRIGGER or ALTER TRIGGER statement.
• A global variable can be qualified with the schema implicitly or explicitly specified when the global variable is created.

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 function or SQL procedure at the time the function or procedure is created, the name is first checked as a column.
• If not found as a column, the name is checked as an SQL variable, SQL parameter, or transition variable. An 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, an SQL variable and an SQL parameter, or an SQL variable and a transition variable, 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 is checked as an SQL parameter name or transition variable.
• If not found as an SQL parameter or transition variable, the name is checked as a global variable.

If the name is still not resolved as a column, SQL variable, SQL parameter, global variable, or transition variable and the scope of the name included a table or view that does not exist at the current server, it is assumed to be a column. If all the tables and views exist at the current server, it is assumed to be a global variable. Otherwise, an error is returned.

The name of an SQL variable, SQL parameter, transition variable, or global variable in an SQL function, SQL procedure, or trigger can be the same as an identifier used as a name in certain SQL statements. Qualified names for SQL variables, SQL parameters, or transition variables are not supported in these statements. The following rules describe how the name is resolved in these statements.

• In the SET PATH or SET SCHEMA statements, the name is checked as an SQL variable, SQL parameter, transition variable, or global variable. If an SQL variable, SQL parameter, transition variable, or global variable by that name is not found, the name is assumed to be a schema name.
• In the CONNECT, RELEASE, and SET CONNECTION statements, the name is used as a server name.
• In the CALL statement, the name is used as the procedure name.
• In the ASSOCIATE LOCATORS, or DESCRIBE PROCEDURE statement, the name is used as the procedure name.