Blocks in Snowflake Scripting

Snowflake scripting allows you to write procedural code within the Snowflake scripting block. In this article, we will check how to use blocks in a Snowflake scripting.

Block in a Snowflake scripting is defined by a keywords such as DECLARE, BEGIN, EXCEPTION and END. You usually write your procedural code into different part.

Structure of Block in a Snowflake Scripting

Snowflake scripting block has the following basic structure:

DECLARE
  ... variable declarations, ... 
  ... cursor declarations, etc ...
BEGIN
  ... Snowflake Scripting and SQL statements ...
EXCEPTION
  ... statements for handling exceptions ...
END;

Where;

DECLARE: Allows you to declare variables, cursor, etc. Snowflake scripting also allows you to declare variables in the BEGIN and END block.

You can declare;

Variables
Cursors
Resulsets
Exceptions

And declare block is optional in Snowflake scripting.

BEGIN and END: This blocks allows you to write SQL statements and Snowflake Scripting constructs such as branching and looping structures. You can also declare variables in this section.
EXCEPTION: This block allows you to handle exceptions if any. Similar to DECALRE block, EXCEPTION block is optional.

Types of Blocks in Snowflake Scripting

Snowflake support following blocks in scripting.

Block in a Stored Procedure
Anonymous Block

Blocks in a Snowflake Stored Procedure

Stored procedure encapsulates the business logic and can handle far more complex logics such as branching and looping, returning results, error handling, writing dynamic SQL statements, etc.

The Snowflake scripting stored procedure is a collection of blocks such as DECLARE, BEGIN, EXCEPTION and END.

The following is an example that uses various blocks.

create or replace procedure proc_area()
  returns float
  language sql
  as
    declare
      radius float;
      area_of_circle float;
    begin
      radius := 3;
      area_of_circle := pi() * radius * radius;
      return area_of_circle;
  exception
  when other then
    return object_construct('Error type', 'Other error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);
    end;
    
call proc_area();

Anonymous Blocks in Snowflake Script

An anonymous block is a block which defined outside stored procedure. If you don’t want to store the block in a stored procedure, you can define and use an anonymous block. It allows you to define the block as a separate, standalone SQL statement.

The following is an example of an anonymous block

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;
exception
  when other then
    return object_construct('Error type', 'Other error',
                            'SQLCODE', sqlcode,
                            'SQLERRM', sqlerrm,
                            'SQLSTATE', sqlstate);  
end;

The BEGIN and END block that defines the block will also executes the block. You don’t have to call separate CALL statement to execute this block.