Skip to main content

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.

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

Supported parameters

The engine supports the parameters listed below.

Configuration KeyRequired/OptionalData Types and ValuesDescription
return_typeRequiredStringThe return type of the procedure
statementRequiredStringSpecifies the JavaScript / SQL code used to create the procedure
argumentsOptionalObject. See here for a definition of argumentList of the arguments for the procedure
commentOptionalStringSpecifies a comment for the procedure
deletedOptionalBoolean: True enables deletion prevention, False does nothingSpecifies what objects are allowed to be deleted
execute_asOptionalStringSets execute context - see caller's rights and owner's rights
grantsOptionalMap: See Supported Procedure Grants to RolesList of Privileges and Roles to which privileges are granted on the current procedure.
languageOptionalString: javascript (default), sqlSpecifies the language of the stored procedure code
manage_modeOptionalString: all (default), none, grantsConfigures what properties to manage for the procedure.
See Changing Manage Mode before changing the value.
null_input_behaviorOptionalStringSpecifies the behavior of the procedure when called with null inputs
return_behaviorOptionalStringSpecifies 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 KeyRequired / OptionalData Types and ValuesDescription
typeRequiredStringData 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

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

SQL Procedure

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;