Skip to main content

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
caution

During subsequent pipeline runs a force-replacement behavior might be observed in PLAN and APPLY phase for few parameters like return_behavior, null_input_behavior, statement, arguments, return_type, etc.

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​

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        procedures:
          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
Last updated on