Working with variables¶

Declaring a variable¶

Before you can use a variable, you must declare the variable. When you declare a variable, you must specify the type of the variable in one of the following ways:

• Explicitly specify the data type.

• Specify an expression for the initial value of the variable. Snowflake Scripting uses the expression to determine the data type of the variable. See How Snowflake Scripting infers the data type of a variable.

You can declare a variable in the following ways:

• Within the DECLARE section of the block by using any of the following:

  <variable_name> <type> ;
  
  <variable_name> DEFAULT <expression> ;
  
  <variable_name> <type> DEFAULT <expression> ;
  

  Copy

• Within the BEGIN … END section of the block (before you use the variable) by using the LET command in any of the following ways:

  LET <variable_name> <type> { DEFAULT | := } <expression> ;
  
  LET <variable_name> { DEFAULT | := } <expression> ;
  

  Copy

Where:

variable_name

The name of the variable. The name must follow the naming rules for Object Identifiers.

type

The data type of the variable. The data type can be any of the following:

• A SQL data type

• CURSOR

• RESULTSET

• EXCEPTION

DEFAULT expression or . := expression

Assigns the value of expression to the variable.

If both type and expression are specified, the expression must evaluate to a data type that matches. If the types do not match, you can cast the value to the specified type.

The following example declares variables in the DECLARE section and in the BEGIN … END section of the block:

DECLARE
  profit number(38, 2) DEFAULT 0.0;
BEGIN
  LET cost number(38, 2) := 100.0;
  LET revenue number(38, 2) DEFAULT 110.0;

  profit := revenue - cost;
  RETURN profit;
END;

Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

EXECUTE IMMEDIATE 
$$
DECLARE
  profit number(38, 2) DEFAULT 0.0;
BEGIN
  LET cost number(38, 2) := 100.0;
  LET revenue number(38, 2) DEFAULT 110.0;

  profit := revenue - cost;
  RETURN profit;
END;
$$
;

+-----------------+
| anonymous block |
|-----------------|
|           10.00 |
+-----------------+

The next sections explain how the data type and scope of a variable are determined:

• How Snowflake Scripting infers the data type of a variable

• Understanding the scope of declarations

For information about assigning a value to a variable, see Assigning a value to a declared variable.

Assigning a value to a declared variable¶

To assign a value to a variable that has already been declared, use the := operator:

<variable_name> := <expression> ;

Where:

variable_name

The name of the variable. The name must follow the naming rules for Object Identifiers.

expression

The expression is evaluated and the resulting value is assigned to the variable.

The expression must evaluate to a data type that matches the type of the variable. If the expression does not match the type, you can cast the value to the type of the variable.

In the expression, you can use functions, including built-in SQL functions and UDFs (user-defined functions).

Using a variable¶

You can use variables in expressions and with Snowflake Scripting language elements (such as RETURN). For example, the code below uses the variables revenue and cost in an expression and the variable profit in a RETURN statement:

DECLARE
  profit NUMBER(38, 2);
  revenue NUMBER(38, 2);
  cost NUMBER(38, 2);
BEGIN
  ...
  profit := revenue - cost;
  ...
RETURN profit;

Using a variable in a SQL statement (binding)¶

You can use a variable in a SQL statement, which is sometimes referred to as binding a variable. To do so, prefix the variable name with a colon. For example:

INSERT INTO my_table (x) VALUES (:my_variable)

If you are using the variable as the name of an object (e.g. the name of a table in the FROM clause of a SELECT statement), use the IDENTIFIER keyword to indicate that the variable represents an object identifier. For example:

SELECT COUNT(*) FROM IDENTIFIER(:table_name)

If you are using a variable in an expression or with a Snowflake Scripting language element (e.g. RETURN), you do not need to prefix the variable with a colon.

For example, you do not need the colon prefix in the following cases:

• You are using the variable with RETURN. In this example, the variable profit is used with a Snowflake Scripting language element and does not need the colon prefix.

  RETURN profit;
  

  Copy

• You are building a string containing a SQL statement to execute. In this example, the variable id_variable is used in an expression and does not need the colon prefix.

  LET select_statement := 'SELECT * FROM invoices WHERE id = ' || id_variable;
  

  Copy

Setting variables to the results of a SELECT statement¶

In a Snowflake Scripting block, you can use the INTO clause to set variables to the values of expressions specified in a SELECT clause:

SELECT <expression1>, <expression2>, ... INTO :<variable1>, :<variable2>, ... FROM ... WHERE ...;

When you use this syntax:

• variable1 is set to the value of expression1.

• variable2 is set to the value of expression2.

The SELECT statement must return a single row.

The following example contains a SELECT statement that returns a single row. The example relies on data from this table:

CREATE OR REPLACE TABLE some_data (id INTEGER, name VARCHAR);
INSERT INTO some_data (id, name) VALUES
  (1, 'a'),
  (2, 'b');

The example sets the Snowflake Scripting variables id and name to the values returned for the columns with those names.

DECLARE
  id INTEGER;
  name VARCHAR;
BEGIN
  SELECT id, name INTO :id, :name FROM some_data WHERE id = 1;
  RETURN id || ' ' || name;
END;

Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

EXECUTE IMMEDIATE $$
DECLARE
  id INTEGER;
  name VARCHAR;
BEGIN
  SELECT id, name INTO :id, :name FROM some_data WHERE id = 1;
  RETURN :id || ' ' || :name;
END;
$$
;

The example prints out the id and name from the row returned by the SELECT statement.

+-----------------+
| anonymous block |
|-----------------|
| 1 a             |
+-----------------+

Setting a variable to the return value of a stored procedure¶

See Using the value returned from a stored procedure call.

Examples of using variables¶

The following example shows how to declare a variable, assign a value or expression to a variable, and cast a value to the data type of a variable:

DECLARE
  w INTEGER;
  x INTEGER DEFAULT 0;
  dt DATE;
  result_string VARCHAR;
BEGIN
  w := 1;                     -- Assign a value.
  w := 24 * 7;                -- Assign the result of an expression.
  dt := '2020-09-30'::DATE;   -- Explicit cast.
  dt := '2020-09-30';         -- Implicit cast.
  result_string := w::VARCHAR || ', ' || dt::VARCHAR;
  RETURN result_string;
END;

Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

EXECUTE IMMEDIATE $$
DECLARE
    w INTEGER;
    x INTEGER DEFAULT 0;
    dt DATE;
    result_string VARCHAR;
BEGIN
    w := 1;                     -- Assign a value.
    w := 24 * 7;                -- Assign the result of an expression.
    dt := '2020-09-30'::DATE;   -- Explicit cast.
    dt := '2020-09-30';         -- Implicit cast.
    result_string := w::VARCHAR || ', ' || dt::VARCHAR;
    RETURN result_string;
END;
$$
;

+-----------------+
| anonymous block |
|-----------------|
| 168, 2020-09-30 |
+-----------------+

The following example uses a built-in SQL function in the expression:

my_variable := SQRT(variable_x);

The following declaration implicitly specifies the data types of the variables profit, cost, and revenue by specifying an initial value of the intended data type for each variable.

The example also demonstrates how to use the LET statement to declare the cost and revenue variables outside of the DECLARE portion of the block:

DECLARE
  profit number(38, 2) DEFAULT 0.0;
BEGIN
  LET cost number(38, 2) := 100.0;
  LET revenue number(38, 2) DEFAULT 110.0;

  profit := revenue - cost;
  RETURN profit;
END;

Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

EXECUTE IMMEDIATE $$
DECLARE
    profit DEFAULT 0.0;
BEGIN
    LET cost := 100.0;
    LET revenue DEFAULT 110.0;
    profit := revenue - cost;
    RETURN profit;
END;
$$
;

+-----------------+
| anonymous block |
|-----------------|
|              10 |
+-----------------+

The following example demonstrates the scope of a variable. This example includes two variables and a parameter that all have the same name but different scope.

The example contains three blocks: the outermost, middle, and innermost blocks.

• Within the innermost block, PV_NAME resolves to the variable declared and set in that innermost block (which is set to innermost block variable).

• Within the middle block (and outside of the innermost block), PV_NAME resolves to the variable declared and set in the middle block (which is set to middle block variable).

• Within the outermost block (and outside any of the nested blocks), PV_NAME resolves to the parameter passed to the stored procedure (which is set to parameter by the CALL statement).

The example relies on this table:

CREATE OR REPLACE TABLE names (v VARCHAR);

In this example, the assignment of the string innermost block variable to PV_NAME in the innermost block does not affect the value of the variable in the middle block. The variable in the innermost block is different from the variable in the middle block, even if both variables have the same name.

CREATE OR REPLACE PROCEDURE duplicate_name(pv_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
  DECLARE
    PV_NAME VARCHAR;
  BEGIN
    PV_NAME := 'middle block variable';
    DECLARE
      PV_NAME VARCHAR;
    BEGIN
      PV_NAME := 'innermost block variable';
      INSERT INTO names (v) VALUES (:PV_NAME);
    END;
    -- Because the innermost and middle blocks have separate variables
    -- named "pv_name", the INSERT below inserts the value
    -- 'middle block variable'.
    INSERT INTO names (v) VALUES (:PV_NAME);
  END;
  -- This inserts the value of the input parameter.
  INSERT INTO names (v) VALUES (:PV_NAME);
  RETURN 'Completed.';
END;

Note: If you are using SnowSQL or the Classic Console, use this example instead (see Using Snowflake Scripting in SnowSQL and the Classic Console):

CREATE OR REPLACE PROCEDURE duplicate_name(pv_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
  DECLARE
    PV_NAME VARCHAR;
  BEGIN
    PV_NAME := 'middle block variable';
    DECLARE
    PV_NAME VARCHAR;
    BEGIN
      PV_NAME := 'innermost block variable';
      INSERT INTO names (v) VALUES (:PV_NAME);
    END;
    -- Because the innermost and middle blocks have separate variables
    -- named "pv_name", the INSERT below inserts the value
    -- 'middle block variable'.
    INSERT INTO names (v) VALUES (:PV_NAME);
  END;
  -- This inserts the value of the input parameter.
  INSERT INTO names (v) VALUES (:PV_NAME);
  RETURN 'Completed.';
END;
$$
;

Call the stored procedure:

CALL duplicate_name('parameter');

Check the values in the table:

SELECT *
    FROM names
    ORDER BY v;

+--------------------------+
| V                        |
|--------------------------|
| innermost block variable |
| middle block variable    |
| parameter                |
+--------------------------+

The output shows that:

• In the innermost nested block (which was nested two layers), the inner block’s variable PV_NAME was used.

• In the middle block (which was nested one layer), that middle block’s variable PV_NAME was used.

• In the outermost block, the parameter was used.

For an example of binding a variable when opening a cursor, see the examples of opening cursors.