Welcome To TechBrothersIT: Snowflake SQL Tutorial 146 - The Colon Only Matters In One Place, And This Is It

Snowflake SQL Tutorial 146 - The Colon Only Matters In One Place, And This Is It

Snowflake SQL · Episode 146

In this post we are going to cover variables inside a Snowflake Scripting block.

A variable is a named value that lives for as long as the block runs. You make one with DECLARE or with LET, you fill it with an assignment or with SELECT ... INTO, and you read it back with a colon in front of its name.

That colon is the thing most people are unsure about, and it turns out to matter in exactly one place. Everywhere else you can leave it off and nothing happens at all.

We will start with a block that has no variables in it and build it up step by step. Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 146 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create some sample data
Let's start with a block that has no variables at all
Now let's declare a variable
What does a declared variable actually need?
Now the other way to make one: LET
Can we swap them round?
Now the colon, and the one place it matters
Which means a variable can share a column's name
Now let's fill a variable from a query
Can we assign from a query with no INTO?
Now a block inside a block
What gets caught at create time, and what waits for the call?
One last thing: handing a variable to a function

Let's create some sample data

The blocks in this post read from a table, so we need one first. The script below creates SALES_T146 and fills it.

Sample data

-- Sample data for this post.
-- Six sales rows for the blocks to read from.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE SALES_T146 (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);

INSERT INTO SALES_T146 (SALE_ID, REGION, AMOUNT) VALUES
    (1, 'north', 100.00),
    (2, 'south', 250.50),
    (3, 'east',   75.25),
    (4, 'west',  310.00),
    (5, 'north',  45.00),
    (6, 'south',  88.25);

The insert

INSERT INTO SALES_T146 (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 100.00), (2, 'south', 250.50),
       (3, 'east',   75.25), (4, 'west',  310.00),
       (5, 'north',  45.00), (6, 'south',  88.25);

Result1 row · 1 column

number of rows inserted
6

And here is what that leaves us with, because two of the numbers further down this post come straight out of this table.

Query

SELECT COUNT(*) AS HOW_MANY, SUM(AMOUNT) AS TOTAL_AMOUNT FROM SALES_T146;

Result1 row · 2 columns

HOW_MANY	TOTAL_AMOUNT
6	869.00

Six rows and a total. Now let's write a block.

Let's start with a block that has no variables at all

The smallest Scripting block there is: BEGIN, a RETURN, and END.

Create

CREATE OR REPLACE PROCEDURE PLAIN_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    RETURN 'the block ran';
END;
$$;

Result1 row · 1 column

status
Function PLAIN_T146 successfully created.

Call

CALL PLAIN_T146();

Result1 row · 1 column

PLAIN_T146
the block ran

That is a legal procedure. No variables anywhere, and nothing is missing. It is worth starting here so that everything we add from now on is something we chose to add.

Now let's declare a variable

A DECLARE section goes above the word BEGIN. Each variable gets a name, a type and a default.

Create

CREATE OR REPLACE PROCEDURE DECLARED_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    THE_CUTOFF NUMBER(10,2) DEFAULT 100.00;
    THE_LABEL  VARCHAR      DEFAULT 'the cutoff is ';
BEGIN
    RETURN THE_LABEL || :THE_CUTOFF;
END;
$$;

Result1 row · 1 column

status
Function DECLARED_T146 successfully created.

Call

CALL DECLARED_T146();

Result1 row · 1 column

DECLARED_T146
the cutoff is 100.00

Both variables were read in the RETURN line and both arrived with their defaults in them.

Have a look at that return line again, though, because it is doing something we have not explained yet. One of the two names has a colon in front of it and the other does not, and Snowflake did not complain about either. Hold that thought.

What does a declared variable actually need?

There are two parts to a declaration, the type and the default, so there are three ways to leave something out. Let's try all three.

First a type with no default.

Create

CREATE OR REPLACE PROCEDURE NODEFAULT_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    THE_CUTOFF NUMBER(10,2);
BEGIN
    RETURN 'the value is [' || COALESCE(:THE_CUTOFF::VARCHAR, 'NULL') || ']';
END;
$$;

Result1 row · 1 column

status
Function NODEFAULT_T146 successfully created.

Call

CALL NODEFAULT_T146();

Result1 row · 1 column

NODEFAULT_T146
the value is [NULL]

Null. Not zero, not an empty string, and not an error. An undefaulted variable is null.

About that cell

That cell is a sentence the body built with COALESCE, so the word NULL in it is ordinary text. We will see a real null in a grid further down, and it looks different.

Now the other way round, a default with no type.

Create

CREATE OR REPLACE PROCEDURE NOTYPE_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    THE_CUTOFF DEFAULT 100.00;
BEGIN
    RETURN 'the value is ' || :THE_CUTOFF;
END;
$$;

Result1 row · 1 column

status
Function NOTYPE_T146 successfully created.

Call

CALL NOTYPE_T146();

Result1 row · 1 column

NOTYPE_T146
the value is 100

That works. The default was enough for Snowflake to work out the type for itself.

Which leaves one combination: neither a type nor a default.

Create

CREATE OR REPLACE PROCEDURE NEITHER_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    THE_CUTOFF;
BEGIN
    RETURN 'never reached';
END;
$$;

Query failed — Snowflake returned an error

092229 (P0000): SQL compilation error: error line 3 at position 4
 variable 'THE_CUTOFF' must have either a type or an initializer to infer a type from

092229, and the message says it plainly. A variable must have either a type or something to infer a type from.

So you can leave out the type, or you can leave out the default. You cannot leave out both.

Now the other way to make one: LET

LET makes a variable in the middle of a block, where the statements are. Notice there is no DECLARE section in this one at all.

Create

CREATE OR REPLACE PROCEDURE LETTING_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    LET THE_CUTOFF NUMBER(10,2) := 100.00;
    LET THE_LABEL  VARCHAR      := 'the cutoff is ';
    RETURN :THE_LABEL || :THE_CUTOFF;
END;
$$;

Result1 row · 1 column

status
Function LETTING_T146 successfully created.

Call

CALL LETTING_T146();

Result1 row · 1 column

LETTING_T146
the cutoff is 100.00

The same answer as the declared version gave us. LET needs no DECLARE section.

That one used := to set the value. The word DEFAULT works as well.

Create

CREATE OR REPLACE PROCEDURE LETDEFAULT_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    LET THE_CUTOFF NUMBER(10,2) DEFAULT 100.00;
    RETURN 'the value is ' || :THE_CUTOFF;
END;
$$;

Result1 row · 1 column

status
Function LETDEFAULT_T146 successfully created.

Call

CALL LETDEFAULT_T146();

Result1 row · 1 column

LETDEFAULT_T146
the value is 100.00

Both spellings are accepted, so use whichever reads better to you.

Can we swap them round?

That is the obvious next question. If LET works in the middle, does it work at the top where DECLARE goes?

Create

CREATE OR REPLACE PROCEDURE LETATTOP_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
LET THE_CUTOFF NUMBER(10,2) := 100.00;
BEGIN
    RETURN 'the value is ' || :THE_CUTOFF;
END;
$$;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 2 at position 0 unexpected 'LET'.

001003, syntax error, unexpected LET. Clear enough.

And DECLARE in the middle, where LET goes?

Create

CREATE OR REPLACE PROCEDURE DECLAREINMIDDLE_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    DECLARE THE_CUTOFF NUMBER(10,2) DEFAULT 100.00;
    RETURN 'the value is ' || :THE_CUTOFF;
END;
$$;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 4 at position 11 unexpected ''the value is ''.
syntax error line 5 at position 0 unexpected 'END'.

A syntax error as well, and this one is worth reading twice because the message is misleading. It complains about the string on the line after the declaration, not about the word DECLARE at all.

So if you ever see a syntax error pointing at a line that looks perfectly fine, look at the line above it. The rule underneath both of these is simple: DECLARE belongs above BEGIN, and LET belongs below it.

Now the colon, and the one place it matters

Back to the thought we were holding. Here is a block that reads its variable with no colon in front of it.

Create

CREATE OR REPLACE PROCEDURE NOCOLON_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    THE_CUTOFF NUMBER(10,2) DEFAULT 100.00;
BEGIN
    RETURN 'the value is ' || THE_CUTOFF;
END;
$$;

Result1 row · 1 column

status
Function NOCOLON_T146 successfully created.

Call

CALL NOCOLON_T146();

Result1 row · 1 column

NOCOLON_T146
the value is 100.00

It works. In a plain expression the block finds its own variable either way.

Now let's put the same variable inside a query. With the colon, first.

Create

CREATE OR REPLACE PROCEDURE INQUERY_T146()
RETURNS NUMBER(10,2)
LANGUAGE SQL
AS
$$
DECLARE
    THE_CUTOFF NUMBER(10,2) DEFAULT 100.00;
    THE_TOTAL  NUMBER(12,2);
BEGIN
    SELECT SUM(AMOUNT) INTO :THE_TOTAL
    FROM SALES_T146
    WHERE AMOUNT > :THE_CUTOFF;
    RETURN :THE_TOTAL;
END;
$$;

Result1 row · 1 column

status
Function INQUERY_T146 successfully created.

Call

CALL INQUERY_T146();

Result1 row · 1 column

INQUERY_T146
560.50

That is the total of the rows above the cutoff, and we can check it against the table whenever we like.

Now exactly the same block with the colon taken off, inside the query.

Create

CREATE OR REPLACE PROCEDURE INQUERYNOCOLON_T146()
RETURNS NUMBER(12,2)
LANGUAGE SQL
AS
$$
DECLARE
    THE_CUTOFF NUMBER(10,2) DEFAULT 100.00;
    THE_TOTAL  NUMBER(12,2);
BEGIN
    SELECT SUM(AMOUNT) INTO :THE_TOTAL
    FROM SALES_T146
    WHERE AMOUNT > THE_CUTOFF;
    RETURN :THE_TOTAL;
END;
$$;

Result1 row · 1 column

status
Function INQUERYNOCOLON_T146 successfully created.

Created without a word, which is itself worth noticing. Let's call it.

Call

CALL INQUERYNOCOLON_T146();

Query failed — Snowflake returned an error

000904 (42000): Uncaught exception of type 'STATEMENT_ERROR' on line 6 at position 4 : SQL compilation error: error line 3 at position 19
invalid identifier 'THE_CUTOFF'

000904, invalid identifier. And that is the whole rule.

The rule in three lines

A query resolves a bare name as a column. There is no column by that name, so the query fails.
The colon is what tells the query that this one is a variable and not a column.
Outside a query there are no columns to confuse it with, which is why the colon is optional there.

If the colon decides which one you meant, then a variable named after a column is not a problem at all. Let's prove it with the worst case I could think of.

The table has a column called AMOUNT. So does the block. And the comparison uses both of them, one on each side.

Create

CREATE OR REPLACE PROCEDURE CLASH_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    AMOUNT    NUMBER(10,2) DEFAULT 100.00;
    HOW_MANY  NUMBER;
BEGIN
    SELECT COUNT(*) INTO :HOW_MANY
    FROM SALES_T146
    WHERE AMOUNT > :AMOUNT;
    RETURN 'rows above the variable: ' || :HOW_MANY;
END;
$$;

Result1 row · 1 column

status
Function CLASH_T146 successfully created.

Call

CALL CLASH_T146();

Result1 row · 1 column

CLASH_T146
rows above the variable: 2

The bare AMOUNT was the column and the :AMOUNT was the variable. Let's write the same count with no variable anywhere and compare.

Query

SELECT COUNT(*) AS ROWS_ABOVE_ONE_HUNDRED FROM SALES_T146 WHERE AMOUNT > 100.00;

Result1 row · 1 column

ROWS_ABOVE_ONE_HUNDRED
2

They agree. Nothing clever is happening here, and that is the point.

Now let's fill a variable from a query

SELECT ... INTO puts a query's answer into a variable. Here is one whose query returns exactly one row.

Create

CREATE OR REPLACE PROCEDURE ONEROW_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    THE_REGION VARCHAR;
BEGIN
    SELECT REGION INTO :THE_REGION FROM SALES_T146 WHERE SALE_ID = 1;
    RETURN 'got [' || :THE_REGION || ']';
END;
$$;

Result1 row · 1 column

status
Function ONEROW_T146 successfully created.

Call

CALL ONEROW_T146();

Result1 row · 1 column

ONEROW_T146
got [north]

Straightforward. Now the interesting case: a query that returns no rows at all.

Look at the declaration in this one. The variable starts out holding the word untouched, so we can tell the difference between being left alone and being overwritten.

Create

CREATE OR REPLACE PROCEDURE NOROWS_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    THE_REGION VARCHAR DEFAULT 'untouched';
BEGIN
    SELECT REGION INTO :THE_REGION FROM SALES_T146 WHERE SALE_ID = 999;
    RETURN 'the variable now holds [' || COALESCE(:THE_REGION, 'NULL') || ']';
END;
$$;

Result1 row · 1 column

status
Function NOROWS_T146 successfully created.

Call

CALL NOROWS_T146();

Result1 row · 1 column

NOROWS_T146
the variable now holds [NULL]

Null. So no rows does not leave the variable alone. It overwrites it.

Now the other direction. A query with no WHERE clause over our whole table.

Create

CREATE OR REPLACE PROCEDURE MANYROWS_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    THE_REGION VARCHAR;
BEGIN
    SELECT REGION INTO :THE_REGION FROM SALES_T146;
    RETURN 'got [' || COALESCE(:THE_REGION, 'NULL') || ']';
END;
$$;

Result1 row · 1 column

status
Function MANYROWS_T146 successfully created.

Call

CALL MANYROWS_T146();

Query failed — Snowflake returned an error

092219 (22000): Uncaught exception of type 'STATEMENT_ERROR' on line 5 at position 4 : A SELECT INTO statement expects exactly 1 returned row, but got 6.

092219, and the message counts the rows it got for us. It does not quietly take the first one. It refuses.

And one more thing INTO can do: fill more than one variable from one row.

Create

CREATE OR REPLACE PROCEDURE TWOVARS_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    THE_REGION VARCHAR;
    THE_AMOUNT NUMBER(10,2);
BEGIN
    SELECT REGION, AMOUNT INTO :THE_REGION, :THE_AMOUNT
    FROM SALES_T146 WHERE SALE_ID = 4;
    RETURN :THE_REGION || ' / ' || :THE_AMOUNT;
END;
$$;

Result1 row · 1 column

status
Function TWOVARS_T146 successfully created.

Call

CALL TWOVARS_T146();

Result1 row · 1 column

TWOVARS_T146
west / 310.00

Both arrived, in the order they were listed.

Can we assign from a query with no INTO?

We can. := takes a query on its right hand side as long as the query returns a single value.

Create

CREATE OR REPLACE PROCEDURE ASSIGNED_T146()
RETURNS NUMBER(12,2)
LANGUAGE SQL
AS
$$
DECLARE
    THE_TOTAL NUMBER(12,2);
BEGIN
    THE_TOTAL := (SELECT SUM(AMOUNT) FROM SALES_T146);
    RETURN :THE_TOTAL;
END;
$$;

Result1 row · 1 column

status
Function ASSIGNED_T146 successfully created.

Call

CALL ASSIGNED_T146();

Result1 row · 1 column

ASSIGNED_T146
869.00

That is the total of the whole table, and there is no INTO keyword anywhere in that body.

And assignments run in order, each one seeing what the one before it left behind. Here is a variable that starts at one, has one added, and is then multiplied by ten.

Create

CREATE OR REPLACE PROCEDURE REASSIGNED_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    N NUMBER DEFAULT 1;
BEGIN
    N := :N + 1;
    N := :N * 10;
    RETURN 'ended at ' || :N;
END;
$$;

Result1 row · 1 column

status
Function REASSIGNED_T146 successfully created.

Call

CALL REASSIGNED_T146();

Result1 row · 1 column

REASSIGNED_T146
ended at 20

Which is the answer you get by doing them in that order, and not the answer you get by doing them the other way round.

Now a block inside a block

A BEGIN ... END can sit inside another one. The outer block here declares a variable and the inner one lets its own.

Create

CREATE OR REPLACE PROCEDURE NESTED_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    OUTER_ONE VARCHAR DEFAULT 'outer';
BEGIN
    BEGIN
        LET INNER_ONE VARCHAR := 'inner';
        RETURN :OUTER_ONE || ' sees ' || :INNER_ONE;
    END;
END;
$$;

Result1 row · 1 column

status
Function NESTED_T146 successfully created.

Call

CALL NESTED_T146();

Result1 row · 1 column

NESTED_T146
outer sees inner

The inner block read both of them. So an inner block can see the outer block's variables.

Now the awkward case. An inner variable with the same name as an outer one.

Create

CREATE OR REPLACE PROCEDURE SHADOWED_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    THE_VALUE VARCHAR DEFAULT 'outer';
BEGIN
    BEGIN
        LET THE_VALUE VARCHAR := 'inner';
        RETURN 'inside the inner block it is ' || :THE_VALUE;
    END;
END;
$$;

Result1 row · 1 column

status
Function SHADOWED_T146 successfully created.

Call

CALL SHADOWED_T146();

Result1 row · 1 column

SHADOWED_T146
inside the inner block it is inner

Inside the inner block, the inner one wins.

But the question that actually matters is what happens afterwards. Here is the same thing with the RETURN moved below the inner block.

Create

CREATE OR REPLACE PROCEDURE AFTERSHADOW_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    THE_VALUE VARCHAR DEFAULT 'outer';
BEGIN
    BEGIN
        LET THE_VALUE VARCHAR := 'inner';
    END;
    RETURN 'after the inner block it is ' || :THE_VALUE;
END;
$$;

Result1 row · 1 column

status
Function AFTERSHADOW_T146 successfully created.

Call

CALL AFTERSHADOW_T146();

Result1 row · 1 column

AFTERSHADOW_T146
after the inner block it is outer

The outer value is back. The shadow was scoped to the inner block and the outer variable was never touched. That is what you would want, and it is worth confirming rather than assuming.

What gets caught at create time, and what waits for the call?

We have seen some things refused at create and some at call, so let's do the two clearest cases side by side.

First, a name that was never declared anywhere.

Create

CREATE OR REPLACE PROCEDURE UNDECLARED_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    RETURN 'the value is ' || :NEVER_DECLARED_T146;
END;
$$;

Result1 row · 1 column

status
Function UNDECLARED_T146 successfully created.

Created without a complaint. Now let's call it.

Call

CALL UNDECLARED_T146();

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 3 at position 30
invalid identifier 'NEVER_DECLARED_T146'

000904, invalid identifier, at call time.

Now a number variable with a string assigned to it.

Create

CREATE OR REPLACE PROCEDURE WRONGTYPE_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    N NUMBER DEFAULT 1;
BEGIN
    N := 'not a number';
    RETURN 'it took it: ' || :N;
END;
$$;

Result1 row · 1 column

status
Function WRONGTYPE_T146 successfully created.

Snowflake did not mind that either. And the call?

Call

CALL WRONGTYPE_T146();

Query failed — Snowflake returned an error

100038 (22018): Uncaught exception of type 'EXPRESSION_ERROR' on line 5 at position 9 : Numeric value 'not a number' is not recognized

100038. So the declared type is real, and it is enforced when the assignment runs rather than when the procedure is written.

That is the same pattern as the RETURNS clause in the last post: accepted at create, enforced at call.

And a block with no `RETURN` in it

This one does assign a variable. It just never returns anything.

Create

CREATE OR REPLACE PROCEDURE NORETURN_T146()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    N NUMBER DEFAULT 1;
BEGIN
    N := :N + 1;
END;
$$;

Result1 row · 1 column

status
Function NORETURN_T146 successfully created.

Call

CALL NORETURN_T146();

Result1 row · 1 column

NORETURN_T146
NULL

A null, and this time it is a real one. Compare that grey cell with the one near the top of this post, where the word NULL was part of a sentence the body had built. They look different because they are different.

One last thing: handing a variable to a function

A script variable is just a value, so a function should take one without any ceremony. Here is a scalar function that doubles a number.

Create

CREATE OR REPLACE FUNCTION DOUBLED_T146(N NUMBER)
RETURNS NUMBER
AS
$$
    N * 2
$$;

Result1 row · 1 column

status
Function DOUBLED_T146 successfully created.

And a procedure that declares a variable and passes it in, with the colon, because this is an expression and not a query.

Create

CREATE OR REPLACE PROCEDURE PASSESIN_T146()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
    N NUMBER DEFAULT 21;
BEGIN
    RETURN DOUBLED_T146(:N);
END;
$$;

Result1 row · 1 column

status
Function PASSESIN_T146 successfully created.

Call

CALL PASSESIN_T146();

Result1 row · 1 column

PASSESIN_T146
42

It crossed straight into the function. There is no conversion, no wrapper and nothing to think about. The colon is simply how you spend a variable.

Key points

DECLARE goes above BEGIN. LET goes below it. They are not interchangeable, and either one in the wrong place is 001003.
A declared variable needs a type, or a default, or both. Neither one is 092229.
A type with no default gives you a null. A default with no type is enough for Snowflake to infer the type.
LET accepts := or the word DEFAULT. Both work.
In a plain expression you can read a variable without the colon.
Inside a query you cannot, and that is 000904, invalid identifier.
The reason is that a query resolves a bare name as a column. The colon is what says this one is a variable.
Which is why a variable named after a column is safe. Bare is the column, colon is the variable.
SELECT ... INTO with no rows overwrites your variable with a null rather than leaving it alone.
SELECT ... INTO over many rows refuses with 092219. It will not take the first one.
:= assigns from a single value query with no INTO anywhere.
Assignments run in order, and each one sees what the one before it left.
An inner block sees the outer block's variables, can shadow one by name, and the outer value survives the inner block.
An undeclared name and a wrong typed assignment are both found at call time, not at create time.
A script variable passes into a function as an ordinary value.

Next in this series

In the next post we start branching and looping: IF, CASE as a statement, FOR, WHILE and REPEAT, and what a loop leaves behind when it fails part way through.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. Every count in the text is read off one of the grids on this page. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake Scripting variables documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 146 - The Colon Only Matters In One Place, And This Is It

Let's create some sample data

Let's start with a block that has no variables at all

Now let's declare a variable

What does a declared variable actually need?

Now the other way to make one: LET

Can we swap them round?

Now the colon, and the one place it matters

Now let's fill a variable from a query

Can we assign from a query with no INTO?

Now a block inside a block

What gets caught at create time, and what waits for the call?

And a block with no `RETURN` in it

One last thing: handing a variable to a function

Key points

No comments:

Post a Comment

Label

Snowflake SQL Tutorial 146 - The Colon Only Matters In One Place, And This Is It

Let's create some sample data

Let's start with a block that has no variables at all

Now let's declare a variable

What does a declared variable actually need?

Now the other way to make one: LET

Can we swap them round?

Now the colon, and the one place it matters

Which means a variable can share a column's name

Now let's fill a variable from a query

Can we assign from a query with no INTO?

Now a block inside a block

What gets caught at create time, and what waits for the call?

And a block with no RETURN in it

One last thing: handing a variable to a function

Key points

No comments:

Post a Comment

And a block with no `RETURN` in it