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
• EXECUTE_AS: Sets execute context - see caller's rights and owner's rights.
  • Configuration Key: execute_as
  • Data Type: String
• NULL_INPUT_BEHAVIOR: Specifies the behavior of the procedure when called with null inputs.
  • Configuration Key: null_input_behavior
  • Data Type: String
• RETURN_BEHAVIOR: Specifies the behavior of the procedure when returning results.
  • Configuration Key: return_behavior
  • Data Type: String
• COMMENT: Specifies a comment for the procedure.
  • Configuration Key: comment
  • Data Type: String
• LANGUAGE: Specifies the language of the stored procedure code.
  • Configuration Key: language
  • Data Type: String
  • Possible Values:
    • javascript
    • sql
  • Default Value: javascript
• MANAGE_MODE: Configures what properties to manage for the procedure.
  • Configuration key: manage_mode
  • Data Type: String
  • Possible Values:
    • none
    • grants
    • all(Default)
• GRANTS: List of Privileges and Roles to which privileges are granted on the current procedure.
  • Configuration key: grants
  • Data Type: Map

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

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

SQL Procedure

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;