Scribd
Upload
64 views2 pages

Snowflake SQL Scripting Examples

The document describes how to write SQL scripts and stored procedures in Snowflake including creating databases and schemas, using roles and warehouses, writing procedures, calling procedures, branching, looping, variables, exception handling, and cursors.

Uploaded by

clouditlab9
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
64 views2 pages

Snowflake SQL Scripting Examples

The document describes how to write SQL scripts and stored procedures in Snowflake including creating databases and schemas, using roles and warehouses, writing procedures, calling procedures, branching, looping, variables, exception handling, and cursors.

Uploaded by

clouditlab9
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

/* CREATE DATABASE AND SCHEMA*/

USE DATABASE LA_DB;


USE SCHEMA LA_SCHEMA;

/* Use acccountadmin role and WH */

USE ROLE ACCOUNTADMIN;

USE WAREHOUSE COMPUTE_WH;

-- Write SQLScript in Procedure

CREATE OR REPLACE PROCEDURE myprocedure()


RETURNS VARCHAR
LANGUAGE SQL
AS
$$
-- Snowflake Scripting code
DECLARE
radius_of_circle FLOAT;
area_of_circle FLOAT;
BEGIN
radius_of_circle := 3;
area_of_circle := pi() * radius_of_circle * radius_of_circle;
RETURN area_of_circle;
END;
$$
;

-- Call Procedure
call myprocedure();

-- Write SQLScript without Procedure

DECLARE
radius_of_circle FLOAT;
area_of_circle FLOAT;
BEGIN
radius_of_circle := 3;
area_of_circle := pi() * radius_of_circle * radius_of_circle;
RETURN area_of_circle;
END;

-- When using old console or SnowSQL add $$

EXECUTE IMMEDIATE $$
DECLARE
radius_of_circle FLOAT;
area_of_circle FLOAT;
BEGIN
radius_of_circle := 3;
area_of_circle := PI() * radius_of_circle * radius_of_circle;
RETURN area_of_circle;
END;
$$
;

-- Branching Construct

BEGIN
LET count := 1;
IF (count < 0) THEN
return 'negative value';
ELSEIF (count = 0) THEN
return 'zero';
ELSE
return 'positive value';
END IF;
END;

--Looping

FOR <counter_variable> IN [ REVERSE ] <start> TO <end> { DO | LOOP }


<statement>;
[ <statement>; ... ]
END { FOR | LOOP } [ <label> ] ;

-- Use variables to find last DML affected any row

BEGIN
CREATE OR REPLACE TABLE my_values(id integer);
-- Insert 3 rows into a table.
INSERT INTO my_values VALUES (1), (2), (3);
-- SQLROWCOUNT is not affected by statements
-- that are not DML statements (e.g. SELECT statements).
SELECT * from my_values;
-- Returns the number of rows affected by
-- the last DML statement (the INSERT statement).
RETURN SQLROWCOUNT;
-- RETURN SQLFOUND; true if the last DML statement affected one or more rows.
-- RETURN SQLNOTFOUND , true if the last DML statement affected zero rows.

END;

-- Result Set

-- Exception Handling

-- Cursor Handling

Common questions

Powered by AI

Looping constructs in Snowflake can be used to iterate over numerical ranges by specifying a start and end value, with an optional REVERSE condition to iterate backward. These constructs are employed with a counter variable and can include multiple statements within the loop body . Such loops are particularly useful in operations needing repetitive action over datasets, such as applying transformations, generating test data, or performing batch updates .

Exception handling in Snowflake scripts enhances robustness by providing a mechanism to catch and manage runtime errors, avoiding unexpected termination of database operations. Typical patterns include using TRY...CATCH blocks to trap and handle errors, ensuring that custom error messages or corrective actions are implemented . This contributes to better error diagnostics and increases the reliability of automated processes by enabling scripts to recover from failures gracefully .

Using a stored procedure in Snowflake, as shown in the creation of 'myprocedure', establishes a reusable database object that encapsulates SQL logic for calculation . It can be called multiple times without rewriting the SQL logic. Stored procedures also allow for better error handling and transaction control. On the other hand, direct SQL scripting, which executes inline without encapsulation, is faster for ad-hoc computations but harder to maintain as logic needs to be rewritten each time .

The SQLROWCOUNT variable in Snowflake scripts returns the number of rows affected by the last executed DML statement, which is useful for auditing and validating the outcome of data modification operations . It allows developers to programmatically check if an operation such as INSERT, UPDATE, or DELETE achieved the expected effect, thus enhancing data accuracy and management tasks in automated scripts .

Using older Snowflake consoles or SnowSQL can pose challenges when managing advanced scripting features like EXECUTE IMMEDIATE. These challenges may include compatibility issues, limited feature support, and differences in how SQL scripts and dynamic execution are processed . Additionally, older interfaces may not support the latest scripting enhancements or error handling capabilities, potentially leading to less efficient execution and debugging processes, necessitating adaptations or workarounds to achieve desired outcomes .

In Snowflake, schemas and databases are essential for organizing and managing data within a structured environment. A database is a foundational logical container that holds schemas, which in turn organize data into tables and other objects . Proper organization through schemas aids in scoping SQL queries and improves data security by isolating access based on schema permissions, thereby enhancing overall data architecture management .

Cursor handling in Snowflake allows scripts to process dataset rows individually by declaring cursors that fetch rows one at a time from result sets . This row-by-row processing is beneficial in scenarios requiring fine-grained data manipulation or when working with large datasets that would be inefficient to load all at once into memory. It provides improved memory management, precision in data handling, and the ability to apply complex transformation or validation logic on each row .

Snowflake supports the execution of dynamic SQL statements using the EXECUTE IMMEDIATE command, allowing scripts to compile and run SQL commands stored as strings at runtime . This capability enhances script flexibility by enabling the construction of SQL statements dynamically based on variable content or conditional logic, allowing for more adaptive and responsive database operations .

The branching construct in Snowflake scripting employs conditional logic using IF, ELSE IF, and ELSE statements to evaluate numerical conditions as shown in the script that checks whether a number is negative, zero, or positive . This structure supports precise control over script execution pathways, improving decision-making capabilities in automated processes. The implications for script logic include increased flexibility and complexity in programming, allowing scripts to handle multiple conditions and scenarios efficiently .

The use of roles, such as ACCOUNTADMIN, and warehouses like COMPUTE_WH, determines the permissions and resources available for script execution in Snowflake . Roles control data access and execution privileges, affecting what procedures can be run and who can execute them. Warehouses provide necessary computational resources, impacting the script's performance and execution time, as they dictate how system resources are allocated and tasks are processed .

You might also like