you have had the ability to create stored procedures.
However, this capability was limited to using JavaScript to write those stored procedures.
face a steep learning curve.
What is Snowflake Scripting?
For example, you can now write conditional blocks:
INSERT INTO TEST(LINE) VALUES(‘FIST LINE’);
EXECUTE IMMEDIATE $$
DECLARE
COUNT INT;
BEGIN
SELECT COUNT(*) INTO :COUNT FROM TEST;
IF (COUNT < 2) THEN
INSERT INTO TEST(LINE) VALUES(‘SECOND LINE’);
RETURN ‘INSERTED’;
END IF;
RETURN ‘NOT INSERTED. COUNT = ‘ || :COUNT;
END;
$$;
Variable Declaration
Snowflake Scripting provides a declare section just before your
BEGIN/
END block. You can
declare variables in that section. If you want them to have an initial value, you can use the DEFAULT clause. Here’s an example:
DECLARE
VAR1 VARCHAR DEFAULT ‘Hello World’;
BEGIN
return VAR1;
END;
$$;
BEGIN
let VAR1 VARCHAR := ‘Hello World’;
return VAR1;
END;
$$;
Passing variables to SQL statements in Snowflake Scripting
Binding variables is something that was a little more complicated in the JavaScript world. But in Snowflake Scripting, it’s super easy to
pass variables to SQL statements. Just remember to use a semicolon (‘:’) before the variable name as shown here:
$$
BEGIN
let VAR1 VARCHAR := ‘Hello World’;
CREATE OR REPLACE TABLE TEST AS select :VAR1 as LINE;
END;
$$;
Reading values into variables
Retrieving results is also easy. You can
read values into a variable as shown below, but just like before, remember to use a semicolon (‘:’) character before the variable name.
$$
BEGIN
let VAR1 INT := 0;
select 1000 INTO :VAR1;
return VAR1;
END;
$$;
| anonymous block |
|—————–|
| 1000 |
+—————–+
$$
BEGIN
CREATE OR REPLACE TABLE MYTABLE as SELECT $1 as ID, $2 as Name FROM VALUES(1,‘John’),(2,‘DeeDee’);
LET res RESULTSET := (select Name from MYTABLE ORDER BY ID);
LET c1 CURSOR for res;
LET all_people VARCHAR := ”;
FOR record IN c1 DO
all_people := all_people || ‘,’ || record.Name;
END FOR;
RETURN all_people;
END
$$;
And it will print something like:
| anonymous block |
|—————–|
| ,John,DeeDee |
+—————–+
RESULTSET. To iterate on the results of a query, you can open a cursor for that
RESULTSET and the user a
FOR with the cursor variable.
Conditional Logic
IF and
CASE. For example:
$$
DECLARE
VAR1 INT DEFAULT 10;
BEGIN
IF (VAR1 > 10) THEN
RETURN ‘more than 10’;
ELSE
RETURN ‘less than 10’;
END IF;
END;
$$;
With this being the result:
super convenient to
simplify some administrative tasks.
For example, I usually have this code on a Snowflake worksheet and I need to change it each time I need to create a test database.
create warehouse database1_wh;
create role database1_role;
grant ownership on database database1 to database1_role;
grant ownership on schema database1.public to database1_role;
grant ownership on warehouse database1_wh to database1_role;
grant role database1_role to user USER1;
$$
declare
client varchar default ‘database1’;
user varchar default ‘user1’;
sql varchar;
begin
execute immediate ‘create database if not exists ‘ || client;
execute immediate ‘create warehouse if not exists ‘ || client || ‘_wh’;
execute immediate ‘create role if not exists ‘ || client || ‘_role’;
execute immediate ‘grant ownership on database ‘ || client || ‘ to ‘ || client || ‘_role’;
execute immediate ‘grant ownership on schema ‘ || client || ‘.public to ‘ || client || ‘_role’;
execute immediate ‘grant ownership on warehouse ‘ || client || ‘_wh to ‘ || client || ‘_role’;
execute immediate ‘grant role ‘ || client || ‘_role to user ‘ || user;
end;
$$;
SnowConvert is being updated so you can start modernizing your
Oracle,
Teradata, and SQL Server to Snowflake Scripting. So we hope you enjoy it as much as I am enjoying it.
