CREATE VARIABLE Statement

Quick Links:

Syntax

CREATE [OR REPLACE] VARIABLE identifier data-type 
   [{= | DEFAULT} initial-value]

initial-value - (back to Syntax)
  special-value 
  | string
  | [ - ] number 
  | ( constant-expression ) 
  | built-in-function ( constant-expression ) 
  | NULL

special-value - (back to initial-value)
   CURRENT 
  { DATABASE 
     | DATE 
     | PUBLISHER 
     | TIME 
     | TIMESTAMP 
     | USER 
     | UTC TIMESTAMP } 
  | USER

Parameters

(back to top)

OR REPLACE – pecifying the OR REPLACE clause drops the named variable if it already exists and replaces its definition. You can use the OR REPLACE clause as an alternative to the VAREXISTS function in SQL scripts.

initial-value – the data type must match the type defined by data-type.
If you specify initial-value, the variable is set to that value. If you do not specify initial-value, the variable contains the NULL value until a SET statement assigns a different value.

Examples

(back to top)

Example 1 – this code fragment inserts a large text value into the database:

EXEC SQL BEGIN DECLARE SECTION;
char buffer[5000];
EXEC SQL END DECLARE SECTION;
EXEC SQL CREATE VARIABLE hold_blob VARCHAR;
EXEC SQL SET hold_blob = '';
for(;;) {
	/* read some data into buffer ... */
	size = fread( buffer, 1, 5000, fp );
	if( size <= 0 ) break;
	/* add data to blob using concatenation
	Note that concatenation works for binary 
	data too! */
	EXEC SQL SET hold_blob = hold_blob || :buffer;
}
EXEC SQL INSERT INTO some_table VALUES ( 1, hold_blob );
EXEC SQL DROP VARIABLE hold_blob;

Usage

(back to top)

A variable can be used in an SQL expression anywhere a column name is allowed. If a column name exists with the same name as the variable, the variable value is used.

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

Match any aliases specified in the query's SELECT list.
Match column names for any referenced tables.
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. COMMIT or ROLLBACK statements do not affect variables.

Variables created with the CREATE VARIABLE statement persist for a connection even when the statement is issued within a (BEGIN...END) statement. You must use DECLARE to create variables that only persist within a (BEGIN...END) statement, for example, within stored procedures.

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.

Standards

(back to top)

SQL—Vendor extension to ISO/ANSI SQL grammar.
SAP Sybase Database product—Not supported by Adaptive Server.

Permissions

(back to top)

None