> For the complete documentation index, see [llms.txt](https://docs.snowddl.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.snowddl.com/basic/yaml-configs/procedure.md).

# PROCEDURE

Config path: `/<database>/<schema>/procedure/<name>(<dtypes>).yaml`

Example:

```yaml
arguments:
  ticket_no: VARCHAR(13)
  flight_id: NUMBER(10,0)
  boarding_no: NUMBER(10,0)
  seat_no: VARCHAR(4)

returns: BOOLEAN

body: |-
  BEGIN
    INSERT INTO boarding_passes (ticket_no, flight_id, boarding_no, seat_no)
    VALUES (:TICKET_NO, :FLIGHT_ID, :BOARDING_NO, :SEAT_NO);

    RETURN TRUE;
  END;
```

## Schema

* **language** (str) - language of function (default: SQL)
* **arguments** (dict)
  * *{key}* (ident) - argument name
  * *{value}* (str) - argument [data type](/guides/data-types.md)\
    \--- OR ---
  * *{value}* (dict)
    * <mark style="background-color:red;">**type**</mark> (str) - argument [data type](/guides/data-types.md)
    * **default** (str) - default SQL expression for optional argument
* <mark style="background-color:red;">**returns**</mark> (str) - for single return value, return data type\
  \--- OR ---
* <mark style="background-color:red;">**returns**</mark> (dict) - for table return values
  * *{key}* (ident) - return column name
  * *{value}* (str) - return column data type
* **body** (str) - procedure body
* **is\_strict** (bool) - is procedure STRICT (always returns NULL on NULL input)
* **is\_execute\_as\_caller** (bool) - is function executed "as caller" (default "as owner")
* **imports** (list) - files to import (usually JAR packages)
  * *{items}* (dict)
    * **stage** (ident) - name of stage
    * **path** (str) - path to file
* **packages** (list) - Snowflake system packages to import as dependencies
  * *{items}* (str) - name of package, with optional version of package
* **handler** (str) - name of class and method to be called
* **external\_access\_integrations** (list)
  * *{items}* (ident) -  name of [external access integration](/basic/yaml-configs/external-access-integration.md)
* **secrets** (dict)
  * *{key}* (str) - secret variable name used in procedure code
  * *{value}* (ident) - name of [secret](/basic/yaml-configs/secret.md) object
* **comment** (str)&#x20;

## Usage notes

1. Snowflake supports [overloading](https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#overloading-of-names) of procedure names. Multiple procedures may have the same name as long as they have different arguments. It is required to use comma-separated base data types of arguments in config names.\
   \
   For example: `my_procedure(number).yaml`, `my_procedure(varchar,number).yaml`
2. Make sure to read & fully understand ["caller rights" and "owners rights"](https://docs.snowflake.com/en/sql-reference/stored-procedures-rights.html) for stored procedures. It is very important for security.
3. Files for `imports` should be maintained using [STAGE FILES](/basic/yaml-configs/stage-file.md).
4. If function `body` is empty, `handler` and `imports` with pre-compiled JAR or Python code are required.
5. `runtime_version` should be specified as string with explicit double-quotes (e.g. `"3.8"`). Otherwise YAML parser may confuse it with number, which may cause some unwanted effects.
6. You may use [custom YAML tag](/basic/yaml-tag-include.md) `!include` to store procedure body in a separate file instead of storing it inside YAML.
7. In order to omit return values for table procedures, use the "empty dict syntax": `returns: {}`

## Additional privileges

Procedures with without `is_execute_as_caller: True`  are executed with "schema owner role" privileges. If you want to access objects in other schemas, make sure to specify additional owner grant parameters in [SCHEMA](/basic/yaml-configs/schema.md) config. For example:

* `owner_schema_read` - to read objects in other schemas;
* `owner_integration` - to access objects in EXTERNAL STAGE linked to STORAGE INTEGRATION;

## Links

* [CREATE PROCEDURE](https://docs.snowflake.com/en/sql-reference/sql/create-procedure.html)
* [SHOW PROCEDURES](https://docs.snowflake.com/en/sql-reference/sql/show-procedures.html)
* [DESC PROCEDURE](https://docs.snowflake.com/en/sql-reference/sql/desc-procedure.html)
* [Working with Stored Procedures](https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html)
* [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/procedure.py)