SET¶

Syntax¶

SET <var> = <expr>

SET ( <var> [ , <var> ... ] )  = ( <expr> [ , <expr> ... ] )

Parameters¶

var

Specifies the identifier for the variable to initialize.

expr

Specifies the SQL expression for the variable.

Usage Notes¶

• The command supports setting multiple variables in the same statement.

• If the command executing complex expressions, it might require a running virtual warehouse in the session.

• The number of expressions must match the number of variables to initialize.

• The size of string or binary variables is limited to 256 bytes.

• The identifier (i.e. name) for a SQL variable is limited to 256 characters.

• Variable names such as CURRENT or PUBLIC are reserved for future use by Snowflake and cannot be used.

Examples¶

These two examples use constants:

SET V1 = 10;

SET V2 = 'example';

This example sets more than one variable at a time:

SET (V1, V2) = (10, 'example');

This example sets the variable to the value of a non-trivial expression that uses a SQL query:

SET id_threshold = (SELECT COUNT(*)/2 FROM table1);

Copy

The following example shows that if a SET command evaluates all of the expressions on the right-hand side of the assignment operator before setting the first expression on the left-hand side of the operator. Note that the value of the variable named “max” is set based on the old value of “min”, not the new value.

set (min, max) = (40, 70);

Copy

set (min, max) = (50, 2 * $min);
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
select $max;
+------+
| $MAX |
|------|
|   80 |
+------+

Copy