SQL Procedural Variables

NuoDB SQL supports declaring and defining SQL variables for use in SQL stored procedures, user defined functions (UDFs) or triggers.

Syntax

VAR { var_name var_type [ = { sql_expression | ( sql_query_statement ) | ( dynamic_sql_statement ) } ] }
[ ,var_name var_type [ = { sql_expression | ( sql_query_statement ) | ( dynamic_sql_statement ) } ] ]... ;
var_name = { sql_expression | ( sql_query_statement ) | ( dynamic_sql_statement ) };
var_name [ ,var_name ]... = { ( sql_query_statement ) | ( dynamic_sql_statement ) };

Description

Variables can be declared and optionally defined with the VAR keyword. After being declared, assignment to variables is done with the "var_name =" syntax.

Multiple variables can be defined at once with the syntax "var_name [ ,var_name ]... =" and the return value from sql_query_statement or dynamic_sql_statement. In the case where we are assigning the dynamic_sql_statement return value to multiple variables, the dynamic_sql_statement must be a query. The SELECT list in sql_query_statement or dynamic_sql_statement will be assigned to each var_name in the order listed in the SELECT list. The number of var_names to the left of the equation must match the number of items in the SELECT list.

An entire stored procedure is considered a code block, or scope. Variables, having the same name, cannot be redefined within a code block or scope. What this means is that a variable, having the same name, cannot be redefined inside a stored procedure. However, a stored procedure can contain nested scopes. These nested scopes are defined by any of the following:

Variables defined inside a nested scope are valid only within that scope. These variables, inside a nested scope, can be defined with the same name as an already existing variable that was defined outside the nested scope. This nested scope variable is initialized and assigned values based on processing inside the nested scope. Once processing exits the nested scope, this variable's value reverts back to the value it was initially assigned prior to processing inside the nested scope (see Example 2).

One or more SQL variables can also be initialized by a CASE statement. See SQL Procedural Conditional Statements.

Parameters

Examples

One or more of the examples below make use of tables defined in the HOCKEY schema, which is a schema defined in the test database. The test database is created when the QuickStart process is run. See Running the SQL QuickStart.