Stored Procedures
by
Janardhan
Bandi
Agenda
• What is Stored Proc
• Sample Procedure
• UDF Vs Stored Proc
• How to write Stored Proc?
Stored Procedures
• Stored procedures allow you to write procedural code that includes sql statements,
conditional statements, looping statements and cursors.
• Snowflake supports 5 languages for writing procedures.
• Snowflake Scripting(SQL)
• Java Script
• Java
• Scala
• Python
• From a stored procedure, you can return a single value or tabular data.
• Supports branching and looping.
• Dynamically creating a SQL statement and execute it.
Sample Procedure
CREATE or REPLACE PROCEDURE LOAD_TABLE1()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
var rs = snowflake.execute( { sqlText:
`INSERT INTO table1 ("column 1") SELECT 'value 1' AS "column 1" ; `
}
);
return 'Done’ ;
$$
;
UDFs Vs Procedures
Stored Procedures UDF
Stored procedure may or may not return Functions must return results
results (A Value or Table)
Procedures Are Called as independent
statements Functions are called in SQL statements
Values returned by Procedures are not Values returned by Functions can be directly
directly usable in SQL usable in SQL
Single procedure per CALL statement Single SQL statement can call multiple functions
Procedures can execute DDL and DML UDFs do not have access to perform
operations database operations
Thank You
