SET variable
Applies to: 
Databricks SQL Databricks Runtime 14.1 and above
Modifies the value of one or more temporary variables.
To set a configuration parameter use SET config.
Syntax
SET [ VAR | VARIABLE ] { variable_name = { expression | DEFAULT } } [, ...]
SET [ VAR | VARIABLE ] ( variable_name [, ...] ) = ( query ) }
Note: Outside of a compound statement the keywords VAR or VARIABLE are mandatory to disambiguate from a SET config statement.
Within a compound statement VAR or VARIABLE are not allowed.
Parameters
-
Specifies the name of a temporary variable, previously defined in the session or a compound statement.
If the variable cannot be found Databricks raises an UNRESOLVED_VARIABLE error.
If you specify duplicate variables Databricks raises an DUPLICATE_ASSIGNMENTS error.
-
Any well-formed expression computing the new variable value.
-
DEFAULT
Used the default expression of the variable or
NULLif none was specified to compute the new variable value. -
Any well-formed query with the following restrictions:
- The query returns at most one row (ROW_SUBQUERY_TOO_MANY_ROWS).
- The number of columns returned by the query matches the number of specified variable names (ASSIGNMENT_ARITY_MISMATCH).
- Each returned column can be cast to variable at the matching position (CAST_INVALID_INPUT).
If the query returns no rows Databricks sets all specified variables to
NULL.You can use the
DEFAULTkeyword instead of a select-list expression to set a variable to its default.
Examples
> DECLARE VARIABLE myvar1 INT DEFAULT 7;
> DECLARE VARIABLE myvar2 STRING DEFAULT 'hello';
-- Set a SQL variable to a value
> SET VAR myvar1 = 5;
> VALUES (myvar1);
5
-- Set a SQL variable to a value inside of a compound statement
> BEGIN
SET myvar1 = 5;
END;
> VALUES (myvar1);
5
-- Set a SQL variable back to DEFAULT
> SET VARIABLE myvar1 = DEFAULT;
> VALUES (myvar1);
7
-- Set a SQL variable to the result of a scalar subquery.
> SET VARIABLE myvar1 = (SELECT max(c1) FROM VALUES(1), (2) AS T(c1));
> VALUES (myvar1);
2
-- Set multiple variables from a query
> SET VAR (myvar1, myvar2) = (VALUES(10, 'world'));
> VALUES (myvar1, myvar2);
10 world
-- Set multiple variables from expressions
> SET VAR myvar1 = 11, myvar2 = 'hello';
> VALUES (myvar1, myvar2);
11 hello
-- Set multiple variables based on a query
> SET VARIABLE (myvar1, myvar2)
= (SELECT max(c1), CAST(min(c1) AS STRING) FROM VALUES(1), (2) AS T(c1));
> VALUES (myvar1, myvar2);
2 1
-- Assign NULLs on empty query
> SET VAR (myvar1, myvar2)
= (SELECT max(c1), CAST(min(c1) AS STRING) FROM VALUES(1), (2) AS T(c1) HAVING max(c1) = 0);
> VALUES (myvar1, myvar2);
NULL NULL
-- Using a variable with an IDENTIFIER clause
> DECLARE colname STRING;
> SET VAR colname = 'c1';
> SELECT IDENTIFIER(colname) FROM VALUES(1, 2) AS T(c1, c2);
1
> SET VARIABLE colname = 'c2';
> SELECT IDENTIFIER(colname) FROM VALUES(1, 2) AS T(c1, c2);
2
-- Variable defaults are recomputed
> DECLARE VARIABLE val DEFAULT RAND();
> SELECT val;
0.1234...
> SET VARIABLE val = DEFAULT;
> SELECT val;
0.9876...