In Snowflake Scripting, you can use variables in expressions, Snowflake Scripting statements, and SQL statements.
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 thevariable 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 datatype 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
andexpression
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 specifiedtype
.
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;
Copy
Note: If you are using SnowSQL, the Classic Console, or theexecute_stream
or execute_string
method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):
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;$$;
Copy
+-----------------+| 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.
How Snowflake Scripting infers the data type of a variable¶
When you declare a variable without explicitly specifying the data type, Snowflake Scripting infers thedata type from the expression that you assign to the variable.
If you choose to omit the data type from the declaration, note the following:
If the expression can resolve to different data types of different sizes, Snowflake typically chooses the type that is flexible(e.g. FLOAT rather than NUMBER(3, 1)) and has a high storage capacity (e.g. VARCHAR rather than VARCHAR(4)).
For example, if you set a variable to the value
12.3
, Snowflake can choose one of several data types for the variable,including:NUMBER(3, 1)
NUMBER(38, 1)
FLOAT
In this example, Snowflake chooses FLOAT.
If you need a specific data type for a variable (especially a numeric or timestamp type), Snowflake recommends that you specifythe data type explicitly, even if you provide an initial value.
If Snowflake is unable to infer the intended data type, Snowflake reports a SQL compilation error.
For example, the following code declares a variable without explicitly specifying the data type. The code sets the variable tothe value in a cursor.
...FOR current_row IN cursor_1 DO: LET price := current_row.price_column; ...
Copy
When the Snowflake Scripting block is compiled (e.g. when the CREATE PROCEDURE command is executed), the cursor has not beenopened, and the data type of the column in the cursor is unknown. As a result, Snowflake reports a SQL compilation error:
092228 (P0000): SQL compilation error: error line 7 at position 4 variable 'PRICE' cannot have its type inferred from initializer
Copy
Understanding the scope of declarations¶
Snowflake Scripting uses lexical scoping. When avariable for a value, result set, cursor, or exception is declared in the DECLARE section of a block, the scope (or visibility)of the declared object is that block and any blocks nested in that block.
If a block declares an object with the same name as an object declared in an outer block, then within the innerblock (and any blocks inside that block), only the inner block’s object is in scope. When an object name isreferenced, Snowflake looks for the object with that name by starting first in the current block, and then workingoutward one block at a time until an object with a matching name is found.
For example, if an exception is declared inside a stored procedure, the exception’s scope is limited to that storedprocedure. Stored procedures called by that stored procedure cannot raise (or handle) that exception. Storedprocedures that call that procedure cannot handle (or raise) that exception.
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> ;
Copy
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 ofthe variable.
In the expression, you can use functions, including built-in SQL functionsand UDFs (user-defined functions).
Using a variable¶
You can use variables in expressions and with Snowflake Scripting language elements (such asRETURN). For example, the code below uses the variables revenue
andcost
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;
Copy
Using a variable in a SQL statement (binding)¶
You can use a variable in a SQL statement, which is sometimes referred to as bindinga variable. To do so, prefix the variable name with a colon. For example:
INSERT INTO my_table (x) VALUES (:my_variable)
Copy
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), usethe IDENTIFIER keyword to indicate that the variable represents an object identifier.For example:
SELECT COUNT(*) FROM IDENTIFIER(:table_name)
Copy
If you are using a variable in an expression or with aSnowflake 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 languageelement 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 anexpression and does not need the colon prefix.LET select_statement := 'SELECT * FROM invoices WHERE id = ' || id_variable;
Copy
In addition, the TO_QUERY function provides a simple syntax for accepting a SQL stringdirectly in the FROM clause of a SELECT statement. For a comparison of the TO_QUERY function with dynamic SQL,see Constructing SQL at runtime.
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 ofexpressions specified in a SELECT clause:
SELECT <expression1>, <expression2>, ... INTO :<variable1>, :<variable2>, ... FROM ... WHERE ...;
Copy
When you use this syntax:
variable1
is set to the value ofexpression1
.variable2
is set to the value ofexpression2
.
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');
Copy
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;
Copy
Note: If you are using SnowSQL, the Classic Console, or theexecute_stream
or execute_string
method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):
EXECUTE IMMEDIATE $$DECLARE id INTEGER; name VARCHAR;BEGIN SELECT id, name INTO :id, :name FROM some_data WHERE id = 1; RETURN :id || ' ' || :name;END;$$;
Copy
The example prints out the id
and name
from the row returned by the SELECT statement.
+-----------------+| anonymous block ||-----------------|| 1 a |+-----------------+
Copy
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 datatype 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;
Copy
Note: If you are using SnowSQL, the Classic Console, or theexecute_stream
or execute_string
method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):
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;$$;
Copy
+-----------------+| anonymous block ||-----------------|| 168, 2020-09-30 |+-----------------+
The following example uses a built-in SQL function in the expression:
my_variable := SQRT(variable_x);
Copy
The following declaration implicitly specifies the data types of the variables profit
, cost
, and revenue
byspecifying an initial value of the intended data type for each variable.
The example also demonstrates how to use the LET statement to declare thecost
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;
Copy
Note: If you are using SnowSQL, the Classic Console, or theexecute_stream
or execute_string
method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):
EXECUTE IMMEDIATE $$DECLARE profit DEFAULT 0.0;BEGIN LET cost := 100.0; LET revenue DEFAULT 110.0; profit := revenue - cost; RETURN profit;END;$$;
Copy
+-----------------+| anonymous block ||-----------------|| 10 |+-----------------+
The following example demonstrates the scope of a variable. This example includes two variables and a parameter that all have thesame 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 themiddle 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 storedprocedure (which is set to
parameter
by the CALL statement).
The example relies on this table:
CREATE OR REPLACE TABLE names (v VARCHAR);
Copy
In this example, the assignment of the string innermost block variable
to PV_NAME in the innermost block does not affect thevalue 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 VARCHARLANGUAGE SQLASBEGIN 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;
Copy
Note: If you are using SnowSQL, the Classic Console, or theexecute_stream
or execute_string
method in Python Connectorcode, use this example instead (see Using Snowflake Scripting in SnowSQL, the Classic Console, and Python Connector):
CREATE OR REPLACE PROCEDURE duplicate_name(pv_name VARCHAR)RETURNS VARCHARLANGUAGE SQLAS$$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;$$;
Copy
Call the stored procedure:
CALL duplicate_name('parameter');
Copy
Check the values in the table:
SELECT * FROM names ORDER BY v;
Copy
+--------------------------+| 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 theexamples of opening cursors.