CREATE PROCEDURE

This statement is used to create a stored procedure.

A procedure is a group of Scripting statements that can be invoked by name.

Stored procedures offer advantages in development, integrity, security, performance, and memory allocation.

See also DROP PROCEDURE.

Syntax

CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS] folder_name.proc_name (
    [arg_name arg_datatype [DEFAULT default_value]] [, ...])
    RETURNS {result_datatype}
    [[NOT] NULL]
    AS proc_definition...;

Parameters

OR REPLACE: This clause can be used to recreate a procedure if it already exists, allowing you to change the definition without dropping and recreating it manually or regranting privileges. The procedure is redefined and recompiled.

---

IF NOT EXISTS: This clause can be used to only create the procedure if it does not already exist. If this clause is specified and the precedure already exists, the CREATE PROCEDURE statement is just skipped with a warning message. Otherwise, if this clause is omitted and CREATE PROCEDURE is called for an already existing procedure, an error is thrown.

---

folder_name: The name of the folder in which the procedure should be created.

---

proc_name: The name of the procedure.

proc_name must be unique within the specified folder_name. See also Example 1 | Simple Procedures and Identifiers.

proc_name must begin with an alphanumeric character and must not contain spaces or special characters unless the entire name is enclosed in double quotes (e.g., "My Object"). Quoted identifiers are case-sensitive. See also Naming Rules for Database Objects.

---

arg_name arg_datatype: Specifies the name and data type of the input argument. See Supported Data Types by Meta Database and Naming Rules for Database Objects.

---

[DEFAULT default_value] (optional): Specifies a default value for an argument. This can be a literal or an expression. The argument becomes optional.

Optional arguments (those with a DEFAULT ... clause) must be listed after all required arguments.

---

RETURNS ... [NOT NULL]: Specifies the result type returned by the stored procedure.

Even if the procedure does not explicitly return a value, this clause is required.

result_datatype: Defines the return type. See Supported Data Types by Meta Database.

---

AS proc_definition: Contains the code to be executed by the stored procedure. This may include any valid scripting code. See Scripting Reference and Scripting Guide for more.

---

[[NOT] NULL] (optional): Specifies whether the procedure is allowed to return NULL.

The default is NULL (i.e., returning NULL is allowed).

Example 1 | Simple Procedures and Identifiers

CREATE PROCEDURE mkw_doku.proc1(vari STRING)
RETURNS STRING
AS
  BEGIN
    CASE (vari)
      WHEN 'rang 1' THEN
        RETURN 'first';
      WHEN 'rang 2' THEN
        RETURN 'second';
      ELSE
        RETURN 'not found';
    END;
  END;

CALL mkw_doku.proc1('rang 1');

Procedure 'mkw_doku.proc1' was successfully executed