Unlike MS SQL server, Snowflake's stored procedures are written in JavaScript. A stored procedure returns a single value. Although you can run SELECT statements inside a stored procedure, the results must be used within the stored procedure, or be narrowed to a single value to be returned.
Snowflake stored procedures use JavaScript and, in most cases, SQL:
- JavaScript provides the control structures (branching and looping).
- SQL is executed by calling functions in a JavaScript API.
Stored procedures allow:
- Procedural logic (branching and looping), which straight SQL does not support.
- Error handling.
- Dynamically creating a SQL statement and execute it.
- Writing code that executes with the privileges of the role that owns the procedure, rather than with the privileges of the role that runs the procedure. This allows the stored procedure owner to delegate the power to perform specified operations to users who otherwise could not do so. However, there are limitations on these owner’s rights stored procedures.
One common use for stored procedures is to automate a task that requires multiple SQL statements and is performed frequently.
Snowflake API consists of JavaScript objects and the methods in those objects.
