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

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

Procedure in SOLE for Data Products

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

Overloading means you can add multiple functions with the same name but with different arguments. Use a pipe | after the function name. If you add more than two functions, each function's text after the pipe must be unique. See Examples.

Each function must have a unique signature. A signature is generated based on the following parameters:

• Functions FQN (fully qualified name), which is made up of:
  • Database name
  • Schema name
  • External Function name
• Number of arguments
• Data types of arguments
• The sequence of arguments (mainly data type)

The function is treated as duplicated if you have identical values for the above parameters.

caution

Due to a Snowflake limitation, you can't create a UDF and procedure with the same signature in the same schema.

Overloading

- 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

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 in SOLE for Data Products

- 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

Overloading

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        procedures:
          JAVASCRIPT_PROCEDURE|11:
            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(16777216)
          JAVASCRIPT_PROCEDURE|VARCHAR(16777216)|VARCHAR(16777216):
            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(16777216)
              ARG_2:
                type: VARCHAR(16777216)
          JAVASCRIPT_PROCEDURE|VARCHAR(16777216)|VARCHAR(16777216)|DATE:
            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(16777216)
              ARG_2:
                type: VARCHAR(16777216)
              ARG_3:
                type: DATE

SQL Procedure

Procedure in Current Configuration

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 in SOLE for Data Products

- 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;