DECLARE VARIABLE

Applies to: check marked yes Databricks Runtime 14.1 and above

Creates a session private, temporary variable you can reference wherever a constant expression can be used. You can also use variables in combination with the IDENTIFIER clause to parameterize identifiers in SQL statements.

Variables are modified using the SET VARIABLE statement.

Temporary variables cannot be referenced within:

  • a check constraint,

  • a generated column,

  • a default expression,

  • the body of a persisted SQL UDF,

  • the body of a persisted view.

Temporary variables are also called session variables.

Syntax 

DECLARE [ OR REPLACE ] [ VARIABLE ] variable_name
    [ data_type ] [ { DEFEAULT | = } default_expression ]

Parameters

  • OR REPLACE

    If specified, the variable with the same name is replaced.

  • variable_name

    A name for the variable. The name may be qualified with session or system.session. Unless OR REPLACE is specified, the name must be unique within the session.

  • data_type

    Any supported data type. If data_type is omitted, you must specify DEFAULT, and the type is derived from the default_expression.

  • DEFAULT default_expression or = default_expression

    Defines the initial value of the variable after creation. default_expression must be castable to data_type. If no default is specified, the variable is initialized with NULL.

    If expression includes a subquery Databricks raises a INVALID_DEFAULT_VALUE.SUBQUERY_EXPRESSION error.

Examples 

-- Create a variable with a default
> DECLARE VARIABLE myvar INT DEFAULT 5;
> VALUES (myvar);
 5

-- Setting a variable
> SET VAR myvar = (SELECT sum(c1) FROM VALUES(1), (2) AS T(c1);
> VALUES (myvar);
 3

-- Variables are the outermost scope.
> SELECT myvar, t.myvar, session.myvar FROM VALUES(1) AS T(myvar);
  1  1  3

> DROP TEMPORARY VARIABLE myvar;

-- A minimalist variable declaration
> DECLARE myvar = 5;
> VALUES (myvar);
 5

-- 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 VAR colname = 'c2';
> SELECT IDENTIFIER(colname) FROM VALUES(1, 2) AS T(c1, c2);
  2