LET (Snowflake Scripting)

Assigns an expression to a Snowflake Scripting variable, cursor, or RESULTSET.

For more information on variables, cursors, and RESULTSETs, see:

Note

This Snowflake Scripting construct is valid only within a Snowflake Scripting block.

If you run a LET statement outside a block (for example, directly in a worksheet), you get an error similar to syntax error ... unexpected 'LET'. To fix this, wrap the statement in a BEGIN ... END block. For details, see Understanding Snowflake Scripting blocks.

See also:

DECLARE

Syntax

LET { <variable_assignment> | <cursor_assignment> | <resultset_assignment> }

The syntax for each type of assignment is described below in more detail.

Variable assignment syntax

Use the following syntax to assign an expression to a variable.

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

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

Where:

variable_name

The name of the variable. The name must follow the naming rules for object identifiers.

type

A SQL data type.

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.

For example, the following block declares three variables of type NUMBER, with precision set to 38 and scale set to 2, computes a result, and returns it. The variables use either DEFAULT or := to specify a value.

BEGIN
  LET revenue NUMBER(38, 2) DEFAULT 110.0;
  LET cost NUMBER(38, 2) := 100.0;
  LET profit NUMBER(38, 2) := :revenue - :cost;
  RETURN :profit;
END;

For more examples, see:

Cursor assignment syntax

Use one of the following syntaxes to assign an expression to a cursor.

LET <cursor_name> CURSOR FOR <query> ;

LET <cursor_name> CURSOR FOR <resultset_name> ;

Where:

cursor_name

The name to give the cursor. This can be any valid Snowflake identifier that is not already in use in this block. The identifier is used by other cursor-related commands, such as FETCH (Snowflake Scripting).

query

The query that defines the result set that the cursor iterates over.

This can be almost any valid SELECT statement.

resultset_name

The name of the RESULTSET for the cursor to operate on.

The following examples use this table:

CREATE OR REPLACE TABLE invoices (price NUMBER);
INSERT INTO invoices (price) VALUES (11.11), (22.22), (33.33);

For example, the following block declares a cursor for a query, opens it, fetches each row, and accumulates a total:

DECLARE
  total_price FLOAT DEFAULT 0.0;
  c1 CURSOR FOR SELECT price FROM invoices;
BEGIN
  OPEN c1;
  FOR record IN c1 DO
    total_price := total_price + record.price;
  END FOR;
  CLOSE c1;
  RETURN total_price;
END;

You can also declare a cursor that iterates over a RESULTSET:

DECLARE
  res RESULTSET DEFAULT (SELECT price FROM invoices);
  c1 CURSOR FOR res;
BEGIN
  FOR record IN c1 DO
    RETURN record.price;
  END FOR;
END;

For more examples, see Working with cursors.

RESULTSET assignment syntax

Use the following syntax to assign an expression to a RESULTSET.

LET <resultset_name> RESULTSET { DEFAULT | := } ( <query> ) ;

Where:

resultset_name

The name to give the RESULTSET.

The name should be unique within the current scope.

The name must follow the naming rules for Object identifiers.

DEFAULT query or
:= query

Assigns the value of query to the RESULTSET.

For example, the following block declares a RESULTSET and returns it as a table result:

BEGIN
  LET res RESULTSET := (SELECT price FROM invoices WHERE price > 20);
  RETURN TABLE(res);
END;

For more examples, see Working with RESULTSETs.