Share via

CREATE PROCEDURE

Important

This feature is in Public Preview.

Applies to: : 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.

In addition to positional parameter invocation, you can also invoke procedures using named parameter invocation.

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 |
    NOT DETERMINISTIC |
    COMMENT procedure_comment |
    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. You cannot specify this parameter with IF NOT EXISTS.

  • IF NOT EXISTS

    If specified, creates the procedure only when a procedure with that note does not exist already. If a procedure with the same name exists, the statement is ignored. You cannot specify this parameter with OR 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 a unique for all routines (procedures and functions) in the schema.

  • procedure_parameter

    Specifies a parameter of the procedure.

    • parameter_name

      The parameter name must be unique within the procedure.

    • 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 parameters.

    • 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

    The characteristic SQL SECURITY INVOKER and LANGUAGE SQL 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.

    • 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.

    • MODIFIES SQL DATA

      A procedure is assumed to modify SQL data.

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 'forma' THEN SELECT 'Pleased to meet you.';
        END CASE;
    END;
> CALL greeting('informal');
  Hi!

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

> CALL greeting('casual');
  Hello!