Procedure
Configuration can be provided to the Snowflake Object Lifecycle Engine for the following operation with Procedure:
- Manage the Lifecycle of new and existing Procedures
- Manage Grants of a Procedure
Supported Parameters
The engine supports the parameters listed below.
- RETURN_TYPE: The return type of the procedure.
- REQUIRED
- Configuration Key:
return_type
- Data Type: String
- STATEMENT: Specifies the JavaScript / SQL code used to create the procedure.
- REQUIRED
- Configuration Key:
statement
- Data Type: String
- ARGUMENTS: List of the arguments for the procedure.
- Configuration Key:
arguments
- Data Type: Object. See here for definition of Argument
- Configuration Key:
- EXECUTE_AS: Sets execute context - see caller's rights and owner's rights.
- Configuration Key:
execute_as
- Data Type: String
- Configuration Key:
- NULL_INPUT_BEHAVIOR: Specifies the behavior of the procedure when called with null inputs.
- Configuration Key:
null_input_behavior
- Data Type: String
- Configuration Key:
- RETURN_BEHAVIOR: Specifies the behavior of the procedure when returning results.
- Configuration Key:
return_behavior
- Data Type: String
- Configuration Key:
- COMMENT: Specifies a comment for the procedure.
- Configuration Key:
comment
- Data Type: String
- Configuration Key:
- LANGUAGE: Specifies the language of the stored procedure code.
- Configuration Key:
language
- Data Type: String
- Possible Values:
javascript
sql
- Default Value:
javascript
- Configuration Key:
- MANAGE_MODE: Configures what properties to manage for the procedure.
- Configuration key:
manage_mode
- Data Type: String
- Possible Values:
none
grants
all
(Default)
- Configuration key:
- GRANTS: List of Privileges and Roles to which privileges are granted on the current procedure.
- Configuration key:
grants
- Data Type: Map
- Configuration key:
Procedure Arguments
In the arguments parameter, users can specify the arguments for the procedure.
Multiples arguments in an object format can be listed in the arguments
parameter with each having the following supported parameters:
- TYPE: Data type of the Column
- REQUIRED
- Configuration key:
type
- Data Type: String
Example
arguments:
ARG_1:
type: VARCHAR
ARG_2:
type: DATE
Basic syntax
databases:
<database-name>:
schemas:
<schema-name>:
procedures:
<procedure-name>:
<configuration-key>: <value>
grants:
<privilege>:
- <role-name>
- <role-name>
Supported Procedure Grants to Roles
Following is the list of Privilege Grants to Roles that can be specified in the procedure definition
- USAGE
- OWNERSHIP
Examples
- JavaScript Procedure
- SQL Procedure
databases:
DATABASE_1:
schemas:
SCHEMA_1:
functions:
JAVASCRIPT_PROCEDURE:
comment: "Procedure using JavaScript language"
return_type: VARCHAR
language: JavaScript
execute_as: CALLER
null_input_behavior: "RETURNS NULL ON NULL INPUT"
statement: "return 1"
arguments:
ARG_1:
type: VARCHAR
ARG_2:
type: DATE
databases:
DATABASE_1:
schemas:
SCHEMA_1:
functions:
SQL_PROCEDURE:
comment: "Procedure using SQL language"
language: SQL
return_type: VARCHAR
execute_as: CALLER
statement: >-
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;
END;