CREATE PROCEDURE

Applies to: check marked yes Databricks SQL Databricks Runtime 17.0 and above check marked yes Unity Catalog only

Creates a procedure in Unity Catalog that takes or modifies arguments, executes a set of SQL statements, and optionally returns a result set.

Syntax

CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS]
    procedure_name ( [ procedure_parameter [, ...] ] )
    [ characteristic [...] ]
    AS compound_statement

procedure_parameter
    [ IN | OUT | INOUT ] parameter_name data_type
    [ DEFAULT default_expression ] [ COMMENT parameter_comment ]

characteristic
  { LANGUAGE SQL |
    SQL SECURITY { INVOKER | DEFINER } |
    NOT DETERMINISTIC |
    COMMENT procedure_comment |
    DEFAULT COLLATION default_collation_name |
    MODIFIES SQL DATA }

Parameters

  • OR REPLACE

    If specified, a procedure with the same name is replaced. You cannot replace an existing function with a procedure; doing so raises ROUTINE_ALREADY_EXISTS. You cannot specify this parameter with IF NOT EXISTS; specifying both raises INVALID_SQL_SYNTAX.CREATE_ROUTINE_WITH_IF_NOT_EXISTS_AND_REPLACE.

  • IF NOT EXISTS

    If specified, creates the procedure only when a procedure with that name does not exist already. If a procedure with the same name exists, the statement is ignored. You cannot specify this parameter with OR REPLACE; specifying both raises INVALID_SQL_SYNTAX.CREATE_ROUTINE_WITH_IF_NOT_EXISTS_AND_REPLACE.

  • procedure_name

    A name for the procedure. You can optionally qualify the procedure name with a schema name. If the name is not qualified the permanent procedure is created in the current schema.

    The procedure name must be unique for all routines (procedures and functions) in the schema. If a routine with the same name exists and neither OR REPLACE nor IF NOT EXISTS is specified, Azure Databricks raises ROUTINE_ALREADY_EXISTS.

  • procedure_parameter

    Specifies a parameter of the procedure.

    • parameter_name

      The parameter name must be unique within the procedure; otherwise Azure Databricks raises DUPLICATE_ROUTINE_PARAMETER_NAMES.

    • IN, INOUT, or OUT

      Optionally describes the mode of the parameter.

      • IN

        Defines an input only parameter. This is the default.

      • INOUT

        Defines a parameter that accepts an input-output argument. If the procedure completes without an unhandled error, it will return the final parameter value as an output.

      • OUT

        Defines an output parameter. The parameter is initialized to NULL and, if the procedure completes without an unhandled error, it will return the final parameter value as an output.

    • data_type

      Any supported data type.

    • DEFAULT default_expression

      An optional default to be used when a function invocation does not assign an argument to the parameter. default_expression must be castable to data_type. The expression must not reference another parameter or contain a subquery.

      When you specify a default for one parameter, all following parameters must also have a default.

      DEFAULT is not supported for OUT or INOUT parameters; specifying one raises PROCEDURE_CREATION_PARAMETER_OUT_INOUT_WITH_DEFAULT.

    • COMMENT comment

      An optional description of the parameter. comment must be a STRING literal.

  • compound_statement

    A SQL compound statement (BEGIN ... END) with the definition of the SQL Procedure.

    When the procedure is created syntactic correctness is validated. The procedure body is not validated for semantic correctness until the procedure is invoked.

  • characteristic

    One of SQL SECURITY INVOKER or SQL SECURITY DEFINER, and LANGUAGE SQL are required. All others are optional. You can specify any number of characteristics in any order, but you can specify each clause only once.

    • LANGUAGE SQL

      The language of the function implementation.

    • SQL SECURITY INVOKER

      Specifies that any SQL statements in the body of the procedure will be executed under the authority of the user invoking the procedure.

      When resolving relations and routines within the body of the procedure, Azure Databricks uses the current catalog and the current schema at time of invocation.

      See Authorized user and session user for how the authorized user and the session user behave inside procedure bodies and across nested calls.

    • SQL SECURITY DEFINER

      Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 18.3 and above

      Specifies that any SQL statements in the body of the procedure are always executed under the authority of the owner (definer) of the procedure, regardless of which user invokes the procedure. That is, the owner is the authorized user for the body. The invoker only requires the EXECUTE privilege on the procedure; all access checks on relations, routines, and other objects referenced from the body are evaluated against the authorized user.

      When resolving relations and routines within the body of the procedure, Azure Databricks uses the catalog and schema that were current at the time the procedure was created. The invoker's session-scoped objects, such as temporary views, temporary tables, session variables, and session-scoped functions, are excluded from the resolution search path inside the body, so they cannot be referenced by their unqualified names. They remain accessible when referenced with the session schema qualifier, for example session.object_name or system.session.object_name.

      SQL configurations (for example, ANSI_MODE or the default time zone) that affect the semantics of statements in the body are also captured at creation time and used during every invocation of the procedure, regardless of the invoker's session settings.

      Inside a SQL SECURITY DEFINER body, current_catalog returns the catalog that was current when the procedure was created, and current_schema and current_database return the schema that was current when the procedure was created.

      SQL SECURITY DEFINER does not change the value of session_user: it continues to return the user who issued the CALL. See Authorized user and session user for how the authorized user and the session user differ inside a SQL SECURITY DEFINER body.

    • NOT DETERMINISTIC

      A procedure is assumed nondeterministic, meaning it can return different results on each invocation, even when it's called with the same arguments.

    • COMMENT procedure_comment

      A comment for the procedure. procedure_comment must be STRING literal. The default is NULL.

    • DEFAULT COLLATION default_collation_name

      Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 17.1 and above

      Sets the default collation of the procedure. The default collation of the procedure is used as the default collation for procedure parameters, DEFAULT expressions for parameters, STRING typed local variables declared in the procedure body, and STRING literals used in the procedure body.

      In Databricks Runtime 17.1 through Databricks Runtime 18.2, default_collation_name must be UTF8_BINARY. This clause is mandatory if the schema in which the procedure is created has a default collation other than UTF8_BINARY.

      Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 18 and above

      default_collation_name can be any supported collation name.

      If not specified, the default collation is derived from the schema in which the procedure is created.

    • MODIFIES SQL DATA

      A procedure is assumed to modify SQL data.

Common error conditions

Examples

-- Demonstrate INOUT and OUT parameter usage.
> CREATE OR REPLACE PROCEDURE add(x INT, y INT, OUT sum INT, INOUT total INT)
    LANGUAGE SQL
    SQL SECURITY INVOKER
    COMMENT 'Add two numbers'
    AS BEGIN
        SET sum = x + y;
        SET total = total + sum;
    END;
> DECLARE sum INT;
> DECLARE total INT DEFAULT 0;
> CALL add(1, 2, sum, total);
> SELECT sum, total;
 3 3
> CALL add(3, 4, sum, total);
 7 10

-- The last executed query is the result set of a procedure
> CREATE PROCEDURE greeting(IN mode STRING COMMENT 'informal or formal')
    LANGUAGE SQL
    SQL SECURITY INVOKER
    AS BEGIN
        SELECT 'Hello!';
        CASE mode WHEN 'informal' THEN SELECT 'Hi!';
                  WHEN 'formal' THEN SELECT 'Pleased to meet you.';
        END CASE;
    END;
> CALL greeting('informal');
  Hi!

> CALL greeting('formal');
  Pleased to meet you.

> CALL greeting('casual');
  Hello!

-- Use SQL SECURITY DEFINER so the procedure runs with the owner's privileges
-- and references its creation-time catalog and schema. The invoker only needs
-- EXECUTE on `audit_app.ops.log_event`; they do not need any privileges on the
-- underlying `audit_app.private.audit_log` table.
> USE CATALOG audit_app;
> USE SCHEMA ops;
> CREATE OR REPLACE PROCEDURE log_event(IN event STRING)
    LANGUAGE SQL
    SQL SECURITY DEFINER
    MODIFIES SQL DATA
    AS BEGIN
        INSERT INTO audit_app.private.audit_log
          VALUES (current_user(), current_catalog(), current_schema(), event);
    END;

-- Even when invoked from a different catalog/schema and by a different user,
-- the body still inserts into `audit_app.private.audit_log`, with
-- `current_catalog()` and `current_schema()` returning the values frozen at
-- creation time. `session_user()` is unaffected by `SQL SECURITY DEFINER`
-- and records the actual invoker -- which is what audit logs typically want.
> USE CATALOG sales;
> USE SCHEMA reports;
> CALL audit_app.ops.log_event('checkout_completed');
  • Last updated on