# CREATE PROCEDURE

## Syntax

{% tabs %}
{% tab title="Current" %}

```sql
CREATE
    [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ OUT | INOUT | IN OUT] param_name type |
    [ IN ] param_name type [DEFAULT value or expression]

type:
    Any valid MariaDB data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement
```

The `IN OUT` parameter works only in [Oracle mode](#oracle-mode).
{% endtab %}

{% tab title="< 11.8" %}

```sql
CREATE
    [OR REPLACE]
    [DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MariaDB data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement
```

{% endtab %}
{% endtabs %}

## Description

Creates a [stored procedure](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures). By default, a routine is associated with the default database. To associate the routine explicitly with a given database, specify the name as `db_name.sp_name` when you create it.

When the routine is invoked, an implicit ` USE`` `` `*`db_name`* is performed (and undone when the routine terminates). The causes the routine to have the given default database while it executes. `USE` statements within stored routines are disallowed.

When a stored procedure has been created, you invoke it by using the `CALL` statement (see [CALL](https://mariadb.com/docs/server/reference/sql-statements/stored-routine-statements/call)).

To execute the `CREATE PROCEDURE` statement, it is necessary to have the `CREATE ROUTINE` privilege. By default, MariaDB automatically grants the `ALTER ROUTINE` and `EXECUTE` privileges to the routine creator. See also [Stored Routine Privileges](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions/stored-routine-privileges).

The `DEFINER` and `SQL SECURITY` clauses specify the security context to be used when checking access privileges at routine execution time, as described [here](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions/stored-routine-privileges). Requires the [SET USER](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant#set-user) privilege.

If the routine name is the same as the name of a built-in SQL function, you must use a space between the name and the following parenthesis when defining the routine, or a syntax error occurs. This is also true when you invoke the routine later. For this reason, we suggest that it is better to avoid re-using the names of existing SQL functions for your own stored routines.

The `IGNORE_SPACE` SQL mode applies to built-in functions, not to stored routines. It is always allowable to have spaces after a routine name, regardless of whether `IGNORE_SPACE` is enabled.

The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of `()` should be used. Parameter names are not case sensitive.

Each parameter can be declared to use any valid data type, except that the `COLLATE` attribute cannot be used.

For valid identifiers to use as procedure names, see [Identifier Names](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-names).

### Things to be Aware of With CREATE OR REPLACE

* One can't use `OR REPLACE` together with `IF EXISTS`.

## CREATE PROCEDURE IF NOT EXISTS

If the `IF NOT EXISTS` clause is used, then the procedure will only be created if a procedure with the same name does not already exist. If the procedure already exists, then a warning are triggered by default.

### IN/OUT/INOUT/IN OUT

Each parameter is an `IN` parameter by default. To specify otherwise for a parameter, use the keyword `OUT` or `INOUT` before the parameter name.

An `IN` parameter passes a value into a procedure. The procedure might modify the value, but the modification is not visible to the caller when the procedure returns. An `OUT` parameter passes a value from the procedure back to the caller. Its initial value is `NULL` within the procedure, and its value is visible to the caller when the procedure returns. An `INOUT` parameter is initialized by the caller, can be\
modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns.

For each `OUT` or `INOUT` parameter, pass a user-defined variable in the`CALL` statement that invokes the procedure so that you can obtain its value when the procedure returns. If you are calling the procedure\
from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an `IN` or `INOUT` parameter.

### DEFAULT value or expression

As of [MariaDB 11.8](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/11.8), each parameter can be defined as having a default value or expression. This can be useful if needing to add extra parameters to a procedure which is already in use.

### DETERMINISTIC/NOT DETERMINISTIC

`DETERMINISTIC` and `NOT DETERMINISTIC` apply only to [functions](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions). Specifying `DETERMINISTC` or `NON-DETERMINISTIC` in procedures has no effect. The default value is `NOT DETERMINISTIC`. Functions are `DETERMINISTIC` when they always return the same value for the same input. For example, a truncate or substring function. Any function involving data, therefore, is always `NOT DETERMINISTIC`.

### CONTAINS SQL/NO SQL/READS SQL DATA/MODIFIES SQL DATA

`CONTAINS SQL`, `NO SQL`, `READS SQL DATA`, and `MODIFIES SQL DATA` are informative clauses that tell the server what the function does. MariaDB does not check in any way whether the specified clause is correct. If none of these clauses are specified, `CONTAINS SQL` is used by default.

`MODIFIES SQL DATA` means that the function contains statements that may modify data stored in databases. This happens if the function contains statements like [DELETE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/delete), [UPDATE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/update), [INSERT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert), [REPLACE](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/changing-deleting-data/replace) or DDL.

`READS SQL DATA` means that the function reads data stored in databases but does not modify any data. This happens if [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) statements are used, but there no write operations are executed.

`CONTAINS SQL` means that the function contains at least one SQL statement, but it does not read or write any data stored in a database. Examples include [SET](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set) or [DO](https://mariadb.com/docs/server/reference/sql-statements/stored-routine-statements/do).

`NO SQL` means nothing, because MariaDB does not currently support any language other than SQL.

The routine\_body consists of a valid SQL procedure statement. This can be a simple statement such as [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) or [INSERT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/inserting-loading-data/insert), or it can be a compound statement written using [BEGIN and END](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/begin-end). Compound statements can contain declarations, loops, and other control structure statements. See [Programmatic and Compound Statements](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements) for syntax details.

MariaDB allows routines to contain DDL statements, such as `CREATE` and `DROP`. MariaDB also allows [stored procedures](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures) (but not [stored functions](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions)) to contain SQL transaction statements such as `COMMIT`.

For additional information about statements that are not allowed in stored routines, see [Stored Routine Limitations](https://mariadb.com/docs/server/server-usage/stored-routines/stored-routine-limitations).

### Invoking stored procedure from within programs

For information about invoking [stored procedures](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures) from within programs written in a language that has a MariaDB/MySQL interface, see [CALL](https://mariadb.com/docs/server/reference/sql-statements/stored-routine-statements/call).

### OR REPLACE

If the optional `OR REPLACE` clause is used, it acts as a shortcut for the following statements, with the exception that any existing [privileges](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions/stored-routine-privileges) for the procedure are not dropped:

```sql
DROP PROCEDURE IF EXISTS name;
CREATE PROCEDURE name ...;
```

### sql\_mode

MariaDB stores the [sql\_mode](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#sql_mode) system variable setting that is in effect at the time a routine is created and always executes the routine with this setting in force, regardless of the server [SQL mode](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode) in effect when the routine is invoked.

### Character Sets and Collations

Procedure parameters can be declared with any character set/collation. If the character set and collation are not specifically set, the database defaults at the time of creation are used. If the database defaults change at a later stage, the stored procedure character set/collation will not be changed at the same time; the stored procedure needs to be dropped and recreated to ensure the same character set/collation as the database is used.

### Oracle Mode

A subset of Oracle's PL/SQL language is supported in addition to the traditional SQL/PSM-based MariaDB syntax. See [Oracle mode](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/about/compatibility-and-differences/sql_modeoracle) for details on changes when running Oracle mode.

## Examples

The following example shows a simple stored procedure that uses an `OUT` parameter. It uses the `DELIMITER` command to set a new delimiter for the duration of the process — see [Delimiters in the mariadb client](https://mariadb.com/docs/server/clients-and-utilities/mariadb-client/mariadb-command-line-client#delimiters).

```sql
DELIMITER //

CREATE PROCEDURE simpleproc (OUT param1 INT)
 BEGIN
  SELECT COUNT(*) INTO param1 FROM t;
 END;
//

DELIMITER ;

CALL simpleproc(@a);

SELECT @a;
+------+
| @a   |
+------+
|    1 |
+------+
```

Character set and collation:

```sql
DELIMITER //

CREATE PROCEDURE simpleproc2 (
  OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
 BEGIN
  SELECT CONCAT('a'),f1 INTO param1 FROM t;
 END;
//

DELIMITER ;
```

`CREATE OR REPLACE`:

```sql
DELIMITER //

CREATE PROCEDURE simpleproc2 (
  OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
 BEGIN
  SELECT CONCAT('a'),f1 INTO param1 FROM t;
 END;
//
ERROR 1304 (42000): PROCEDURE simpleproc2 already exists

DELIMITER ;

DELIMITER //

CREATE OR REPLACE PROCEDURE simpleproc2 (
  OUT param1 CHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin'
)
 BEGIN
  SELECT CONCAT('a'),f1 INTO param1 FROM t;
 END;
//
ERROR 1304 (42000): PROCEDURE simpleproc2 already exists

DELIMITER ;
Query OK, 0 rows affected (0.03 sec)
```

## See Also

* [Identifier Names](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-names)
* [Stored Procedure Overview](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/stored-procedure-overview)
* [ALTER PROCEDURE](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/alter-procedure)
* [DROP PROCEDURE](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures/drop-procedure)
* [SHOW CREATE PROCEDURE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-procedure)
* [SHOW PROCEDURE STATUS](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-procedure-status)
* [Stored Routine Privileges](https://mariadb.com/docs/server/server-usage/stored-routines/stored-functions/stored-routine-privileges)
* [Information Schema ROUTINES Table](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-routines-table)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}