Skip to main content

CALL

Preview

This feature is in Public Preview.

Applies to: check marked yes Databricks Runtime 17.0 and above

Invokes a stored procedure by name and passes arguments to and from the procedure.

Procedure calls can be nested up to 64 levels.

Syntax​

CALL procedure_name( [ argument [, …] ]
                     [ namedParameter => argument ] [, ...] )

Parameters​

  • procedure_name

    The name of the procedure to be invoked. The procedure name may be optionally qualified with a schema name. If the name is not qualified the procedure is resolved in the current schema. If the procedure does not exist, a ROUTINE_NOT_FOUND error is raised.

  • argument

    For arguments assigned to IN parameters, any expression of a type which can be cast to the parameter, or the DEFAULT keyword. For OUT and INOUT parameters the argument must be a session variable, or local variable.

    If the procedure fails the argument values of the OUT and INOUT parameters remain unchanged.

  • namedParameter

    The unqualified name of a parameter to which the argExpr is assigned.

The number of arguments must be no greater than the number of parameters specified for the procedure. If you specify fewer arguments than there are parameters, the parameters must be defined with DEFAULT values.

Examples​

SQL
> CREATE OR REPLACE PROCEDURE area_of_rectangle
     (IN x INT, IN y INT, OUT area INT, INOUT acc INT)
    LANGUAGE SQL
    SQL SECURITY INVOKER
    AS BEGIN
      SET area = x * y;
      SET acc = acc + area;
      SELECT 'Area of rectangle is: ' || area;
    END;

-- Session varables to handle OUT and INOUT parameters
> DECLARE area INT;
> DECLARE acc INT DEFAULT 10;

-- Invocation by position
> CALL area_of_rectangle(5, 10, area, acc);
  Area of rectangle is: 50
> SELECT area, acc;
  area  acc
  —---  —--
    50   60

-- Invocation by name
> CALL area_of_rectangle(y => 10, x => 5, area => area, acc => acc);
  Area of rectangle is: 50
> SELECT area, acc;
  area  acc
  —---  —--
    50  120
Last updated on