Purpose of the Article: This blog is written to make it easier for people to understand about snowflake stored procedures
Intended Audience: This blog will help the ones who are working as a snowflake developer
Tools and Technology: Snowflake
Keywords: Snowflake stored procedures, Snowflake stored procedures using Java script, Stored procedures using snowflake scripting
You can store multiple SQL statements in one Stored Procedure and execute them at the same time by calling the Procedure. The Stored Procedure can be created once and executed many times using the CALL command. It allows Procedural logic (branching and looping) and Error handling.
- Stored Procedure can be created by using CREATE command
CREATE OR REPLACE PROCEDURE Procedure_Name()
- Stored Procedure can be executed by using CALL command
CALL Procedure_Name()
In Snowflake, we can write Stored Procedures using JavaScript:
If we want to delete any record from the table, we can delete that record using the Stored Procedure by passing values through the given parameters (Please go through the article, then you will know about the parameters).
Existing records in the table before performing the stored procedure
CREATE OR REPLACE PROCEDURE StoredProc(ID_1 DOUBLE)
RETURNS VARCHAR NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
var my_sql_command ="DELETE FROM TABLENAME WHERE ID= "+ID_1+" ";
var statement1 = snowflake.createStatement({sqlText:my_sql_command});
var result_set = statement1.execute();
return "success";
$$;
CALL StoredProc(4)
Records in the table after performing the Stored Procedure
The above one is the example stored procedure in JavaScript. Here, we have keywords that will perform some actions.
- Snowflake: It can Create statements & execute methods
- Statement: Executes the prepared statements & executes metadata information
- ResultSet: It can be used to act as iterator over individual results
With the use of Snowflake Stored Procedures we can:
- Grant Schema privileges on Stored Procedures
- Make use of procedural logic
- Implement error handling
- Iterate over result sets
Values Passing through Parameters:
We can create Procedures including parameters, to pass the values dynamically.
Syntax: – CREATE OR REPLACE PROCEDURE Procedure_Name(Parameter1 Datatype, Parameter2 Datatype)
Calling the Stored Procedure, which includes parameters
Syntax: – CALL Procedure_Name(Value1, Value2)
The below Procedure is to update data in the table by passing values through parameters.
CREATE OR REPLACE PROCEDURE DynamicInsertQuery(TABLENAME VARCHAR,COLUMNNAME1 VARCHAR,COLUMNNAME2 VARCHAR,VALUE1 VARCHAR,VALUE2 VARCHAR)
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
var my_sql_command = "update "+TABLENAME+" set "+COLUMNNAME1+"='"+VALUE1+"' where "+COLUMNNAME2+"='"+VALUE2+"' ";
var statement1 = snowflake.createStatement({sqlText:my_sql_command});
var rs = statement1.execute();
return “Success”;
$$;
call DynamicInsertQuery('new_table','name','id','fhjdsgh','2')
Result:
Note: – It is mandatory to mention parameters in UPPER CASE within the Stored Procedure.
ERROR HANDLING:
- Using JavaScript Try/Catch block, we can implement error handling in Snowflake Stored Procedures.
- We can execute SQL statements inside a try block. If an error occurs, then the catch block can roll back the statements. The Stored Procedure can throw a pre-defined exception or a custom exception.
Example Stored Procedure :
CREATE OR REPLACE PROCEDURE DynamicInsertQuery(TABLENAME VARCHAR, COLUMNNAME1 VARCHAR, COLUMNNAME2 VARCHAR, VALUE1 VARCHAR, VALUE2 VARCHAR)
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
try
{
var valueArray = [];
var my_sql_command =" update "+TABLENAME+" set "+COLUMNNAME1+"='"+VALUE1+"' where "+COLUMNNAME2+"='"+VALUE2+"' ";
var statement1 = snowflake.createStatement({sqlText:my_sql_command});
var rs = statement1.execute();
while (rs.next())
{
valueArray.push({"error_count":0,"MESSAGE":"successfully updated the record"});
}
}
catch (err) {
valueArray.push({"error_count":1,"MESSAGE":err});
}
return valueArray;
$$;
call DynamicInsertQuery('new_table','name','id','fhjdsgh','2')
Pre-defined exception:
For the above-stored procedure if we get any error during execution, the catch block will throw a Pre-defined exception like the below picture.
Custom Exception:
If you replace the above catch block with an error message like below. The catch block will throw custom exceptions
catch (err)
{
valueArray.push({"error_count":1,"MESSAGE":"Record Not updated"});
}
Writing Stored Procedures using Snowflake Scripting in the classic web interface:
Syntax: –
Create or replace procedure procedure_name(parameter1 varchar)
Returns varchar
Language sql
As
$$
Begin
Return parameter1;
End;
$$;
call procedure_name('hello')
Result:
In the above procedure, we can return a message using parameter. For the above example we have used string literal delimiter ($$), Begin and End.
$$: – It will be considered as a Start and End of the scripting.
Begin: – It will be considered as Starting for a particular block.
End: – It will be considered as Ending for a particular block.
The below example will clearly show about using multiple blocks in the scripting to insert values into multiple tables at a time.
create or replace procedure Data_adding(name varchar,id numeric)
returns varchar
language sql
as
$$
begin
insert into descr(name) values(:NAME);
begin
insert into New_table(id) values(:ID);
end;
return 'success';
end;
$$
;
CALL Data_adding('raghu','900000')
In conclusion, Stored procedures in Snowflake encapsulate and optimize SQL logic efficiently, improving performance and minimizing code duplication. They can iterate, declare results, and have a plethora of SQL statements. Stored procedures can either be executed directly or can be called from other procedures and are created, modified, and executed using SQL statements in Snowflake.
Author Bio:
Indupriya Kasireddy
Associate Software Engineer
I am working as Associate Software Engineer aligned with the Data Engineering team. I bring experience in Snowflake and MS SQL, and in pursuit of learning and exploring new tools and technologies.
