Procedure

You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with procedure:

Manage the lifecycle of new and existing procedures
Manage grants of a procedure

Usage

note

We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. Learn more about SOLE for Data Products which is currently available as a private preview.

Procedure in Current Configuration
Procedure in SOLE for Data Products

databases:
  <database-name>:
    schemas:
      <schema-name>:
        procedures:
          <procedure-name>:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>

- procedure:
    name: <procedure-name>
    <configuration-key>: <value>
    database: rel(database.<database-name>)
    schema: rel(schema.<schema-name>)
    grants:
      <privilege>:
        - rel(role.<role-name>)
        - rel(role.<role-name>)

Supported parameters

The engine supports the parameters listed below.

Configuration Key	Required/Optional	Data Types and Values	Description
`return_type`	Required	String	The return type of the procedure
`statement`	Required	String	Specifies the JavaScript / SQL code used to create the procedure
`arguments`	Optional	Object. See here for a definition of `argument`	List of the arguments for the procedure
`comment`	Optional	String	Specifies a comment for the procedure
`deleted`	Optional	Boolean: `True` enables deletion prevention, `False` does nothing	Specifies what objects are allowed to be deleted
`execute_as`	Optional	String	Sets execute context - see caller's rights and owner's rights
`grants`	Optional	Map: See Supported Procedure Grants to Roles	List of Privileges and Roles to which privileges are granted on the current procedure.
`language`	Optional	String: `javascript` (default), `sql`	Specifies the language of the stored procedure code
`manage_mode`	Optional	String: `all` (default), `none`, `grants`	Configures what properties to manage for the procedure. See Changing Manage Mode before changing the value.
`null_input_behavior`	Optional	String	Specifies the behavior of the procedure when called with null inputs
`return_behavior`	Optional	String	Specifies the behavior of the procedure when returning results

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.

`arguments` parameters

You 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:

Configuration Key	Required / Optional	Data Types and Values	Description
`type`	Required	String	Data type of the Column

Example

arguments:
  ARG_1:
    type: VARCHAR
  ARG_2:
    type: DATE

Supported procedure grants to roles

Following are the privileges you can grant to roles in the procedure definition:

USAGE
OWNERSHIP

Examples

JavaScript Procedure

Procedure in Current Configuration
Procedure in SOLE for Data Products

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

- procedure:
    name: JAVASCRIPT_PROCEDURE
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    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

Procedure in Current Configuration
Procedure in SOLE for Data Products

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        procedures:
          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;

- procedure:
    name: JAVASCRIPT_PROCEDURE
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    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;

Usage​

Supported parameters​

arguments parameters​

Example​

Supported procedure grants to roles​

Examples​

JavaScript Procedure​

SQL Procedure​