CREATE VARIABLE statement

Use this statement to create a SQL variable.

Syntax
CREATE VARIABLE identifier data-type
Remarks

The CREATE VARIABLE statement creates a new variable of the specified data type. The variable contains the NULL value until it is assigned a different value by the SET statement.

A variable can be used in a SQL expression anywhere a column name is allowed. Name resolution is performed as follows:

  1. Match any aliases specified in the query's SELECT list.

  2. Match column names for any referenced tables.

  3. Assume the name is a variable.

Variables belong to the current connection, and disappear when you disconnect from the database or when you use the DROP VARIABLE statement. Variables are not visible to other connections. Variables are not affected by COMMIT or ROLLBACK statements.

Variables are useful for creating large text or binary objects for INSERT or UPDATE statements from embedded SQL programs.

Local variables in procedures and triggers are declared within a compound statement (see Using compound statements).

Permissions

None.

Side effects

None.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

Example

This example creates a variable called first_name, of data type VARCHAR(50).

CREATE VARIABLE first_name VARCHAR(50);

This example creates a variable called 'birthday', of data type DATE.

CREATE VARIABLE birthday DATE;