1. What are Stored Procedures?
Stored procedures allow you to write procedural code that executes business logic by combining multiple SQL statements. In a stored procedure, you can use programmatic constructs to perform branching and looping.
A stored procedure is created with a CREATE PROCEDURE command and is executed with a CALL command.
Snowflake supports writing stored procedures in multiple languages. In this article we will discuss on writing stored procedures using Snowflake SQL Scripting.
2. Stored Procedure Syntax in Snowflake
The following is the basic syntax for creating Stored Procedures in Snowflake.
CREATE OR REPLACE PROCEDURE <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
RETURNS <result_data_type>
LANGUAGE SQL
AS
$$
<procedure_body>
$$
;
Note that you must use string literal delimiters (‘ or $$) around procedure definition(body) if you are creating a Snowflake Scripting procedure in Classic Web Interface or SnowSQL. The string literal delimiters (‘ or $$) are not mandatory when writing procedures in SnowSight.
Let us understand the various parameters in the stored procedure construct.
2.1. NAME <name>
Specifies the name of the stored procedure.
The name must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. “My Procedure”). Identifiers enclosed in double quotes are also case-sensitive.
2.2. INPUT PARAMETERS ( [ <arg_name> <arg_data_type> ] [ , … ] )
A Stored Procedures can be built which takes one or more arguments as input parameters or even without any input parameters.
- The <arg_name> specifies the name of the input argument.
- The <arg_data_type> specifies the SQL data type of the input argument.
-- Stored Procedure with multiple input arguments
CREATE OR REPLACE PROCEDURE my_proc( id NUMBER, name VARCHAR)
-- Stored Procedure with single input argument
CREATE OR REPLACE PROCEDURE my_proc( id NUMBER)
-- Stored Procedure with no input arguments
CREATE OR REPLACE PROCEDURE my_proc()
2.3. RETURNS <result_data_type>
Specifies the type of the result returned by the stored procedure.
CREATE OR REPLACE PROCEDURE my_proc()
RETURNS VARCHAR
2.4. LANGUAGE SQL
Since Snowflake supports stored procedures in multiple languages, the LANGUAGE parameter specifies the language of the stored procedure definition. For Snowflake scripting, the value to the LANGUAGE parameter is passed as SQL.
CREATE OR REPLACE PROCEDURE my_proc()
RETURNS VARCHAR
LANGUAGE SQL
2.5. PROCEDURE BODY
The body defines the code executed by the stored procedure. The procedure definition is mentioned after the AS clause in the stored procedure construct. As mentioned earlier the body is wrapped between $$ string literal delimiters if the procedure scripting is not done in SnowSight.
3. Understanding various sections in Stored Procedure Body
The Stored Procedure Body is made up of multiple sections. The various sections in the stored procedure body are as follows.
DECLARE
... (variable declarations, cursor declarations, etc.) ...
BEGIN
... (Snowflake Scripting and SQL statements) ...
EXCEPTION
... (statements for handling exceptions) ...
END;
3.1. DECLARE
The DECLARE section is used to define any variables, cursors etc. used in the body. Alternatively they can be declared in the BEGIN…END section of the body also.
3.2. BEGIN…END
The SQL statements and scripting constructs are written between the BEGIN and END sections of the body.
3.3. EXCEPTION
The EXCEPTION section of the body is used to hold any exception handling code you wanted to add.
Note that DECLARE and EXCEPTION sections are not mandatory in every procedure definition.
A simple stored procedure body just requires BEGIN and END sections.
BEGIN
CREATE TABLE employees(id NUMBER, firstname VARCHAR);
END;
4. Creating a Stored Procedure in Snowflake
Consider a use case where the requirement is to purge the inactive employees’ data from a database table. Let us build a Stored Procedure which performs this activity.
The below Stored Procedure deletes all records with status field value as ‘INACTIVE’ from the employees table.
CREATE OR REPLACE PROCEDURE purge_data()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
message VARCHAR;
BEGIN
DELETE FROM employees WHERE status = 'INACTIVE';
message := 'Inactive employees data deleted successfully';
RETURN message;
END;
$$
;
Let us break down each block of the stored procedure below to understand better.
- The name of the stored procedure is
purge_data
and do not take any input parameters. - The data type of the return value from store procedure is defined as
varchar
. - The language is defined as
SQL
, the language in which the procedure body is defined. - A variable named
message
of typevarchar
is defined under DECLARE section of the body. - Between BEGIN…END section of the procedure body,
- The statement to delete the records with INACTIVE status is defined. The variable message is assigned a string value. The assignment operator used is
:=
for assigning value to variable.
- The variable message is returned as the output from the stored procedure.
- The statement to delete the records with INACTIVE status is defined. The variable message is assigned a string value. The assignment operator used is
5. Creating a Stored Procedure with Input Parameters
Consider another scenario where you wanted to purge the data from a table based on an input you passed. Let us understand with an example.
The below Stored Procedure deletes all records with status value that matches the value passed as an input through an input parameter in_status
from the employees table.
CREATE OR REPLACE PROCEDURE purge_data_by_status(in_status VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
message VARCHAR;
BEGIN
DELETE FROM employees WHERE status = :in_status;
message := in_status ||' employees data deleted sucessfully';
RETURN message;
END;
$$
;
The above stored procedure is similar to that of the one defined under section-4 of the article except,
- The name of the procedure is
purge_data_by_status
and accepts an input through parameter namedin_status
of type varchar. - The input parameter is used in the SQL statement which deletes the data from employees table. Prefix the input parameter with colon (:in_status) to use in a SQL statement.
- The same input parameter is also used in the string value assigned to message variable indicating records with which status are deleted.
The above stored procedure can be simplified by eliminating the DECLARE section as shown below.
CREATE OR REPLACE PROCEDURE purge_data_by_status(in_status VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
DELETE FROM employees WHERE status = :in_status;
RETURN in_status ||' employees data deleted successfully';
END;
$$
;
6. Calling a Stored Procedure in Snowflake
Use CALL command to execute a stored procedure in Snowflake.
The following is the syntax to CALL command
CALL <procedure_name> ( [ <arg1> , ... ] )
The below image shows calling a stored procedure named purge_data
and the output of the stored procedure call.
CALL purge_data();
The below image shows calling a stored procedure named purge_data_by_status
with a string input parameter ‘INACTIVE’ and the output of the stored procedure call.
CALL purge_data_by_status('INACTIVE');
7. Closing Points
As the name of the article suggests, it is just an introduction to building stored procedures in Snowflake. We have barely scratched the surface in understanding the complete concepts of stored procedures. Nevertheless, I hope it is a good starting point to begin learning Snowflake stored procedures.
Watch out this spaces for more articles covering in-depths concepts of stored procedures in Snowflake.
Checkout other articles related to Snowflake Stored Procedures
Subscribe to our Newsletter !!
Related Articles:
Learn how to find and kill long running queries in Snowflake using the QUERY_HISTORY table functions available under Information Schema.
Snowflake Data Cloud Connector allows connecting to Snowflake from IICS using which you can securely read/write data into Snowflake.
Row-Level Security is a security mechanism that limits the records returned from a database table based on the permissions provided to the currently logged-in user.