SET statement

Assigns a value to a SQL variable.

Syntax

SET [ owner.]identifier = expression

Remarks

The SET statement assigns a new value to a variable. The variable must have been previously created by using a CREATE VARIABLE statement or DECLARE statement, or it must be an OUTPUT parameter for a procedure. The variable name can optionally use the Transact-SQL convention of an @ sign preceding the name. For example: SET @localvar = 42.

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

The owner specification is only for use when setting owned database-scope variables.

Variables are necessary for creating large text or binary objects for INSERT or UPDATE statements from Embedded SQL programs because Embedded SQL host variables are limited to 32767 bytes.

Variables are local to the current connection and disappear when you disconnect from the database or use the DROP VARIABLE statement. They are not affected by COMMIT or ROLLBACK statements.

If you set a database-scope variable, however, the variable persists after a disconnect. When the database is restarted, the value of a database-scope variable reverts to NULL or its default, if defined. The SYSDATABASEVARIABLE system view contains a list of all database-scope variables and their initial values.

You cannot set a database-scope variable owned by another user.

Privileges

No privileges are required to set a self-owned database-scope variable. To set a database-scope variable owned by PUBLIC, you must have the UPDATE PUBLIC DATABASE VARIABLE system privilege.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Part of optional Language Feature P002, "Computational completeness".
Transact-SQL
The SET statement is supported by SAP Adaptive Server Enterprise. In Adaptive Server Enterprise, a single SET statement can be used to assign values to multiple variables, with individual assignment clauses separated by commas.

Example

This simple example shows the creation of a variable called birthday, and sets the date to CURRENT DATE.

CREATE VARIABLE @birthday DATE;
SET @birthday = CURRENT DATE;

The following code fragment inserts a large text value into the database.

size_t  size;
FILE *  fp;

EXEC SQL BEGIN DECLARE SECTION;
DECL_VARCHAR( 5000 ) buffer;
EXEC SQL END DECLARE SECTION;

fp = fopen( "blob.dat", "r" );
EXEC SQL CREATE VARIABLE hold_blob LONG VARCHAR;
EXEC SQL SET hold_blob = '';
for(;;) {
    size = fread( (void *)buffer.array, 1, 5000, fp );
    if( size <= 0 ) break;
    buffer.len = (a_sql_ulen) size;
    EXEC SQL SET hold_blob = hold_blob || :buffer;
}
EXEC SQL INSERT INTO some_table VALUES( 1, hold_blob );
EXEC SQL COMMIT;
EXEC SQL DROP VARIABLE hold_blob;
fclose( fp );