> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# CREATE PROCEDURE

The `CREATE PROCEDURE` [statement](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) defines a [stored procedure](https://docs.cockroachlabs.com/docs/v24.2/stored-procedures).

## Required privileges

* To define a procedure, a user must have [`CREATE` privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges) on the schema of the procedure.
* To define a procedure with a [user-defined type](https://docs.cockroachlabs.com/docs/v24.2/create-type), a user must have [`USAGE` privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges) on the user-defined type.
* To resolve a procedure, a user must have at least the [`USAGE` privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges) on the schema of the procedure.
* To [call a procedure](https://docs.cockroachlabs.com/docs/v24.2/call), a user must have [`EXECUTE` privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#supported-privileges) on the procedure.
* At procedure definition and execution time, a user must have privileges on all the objects referenced in the procedure body. Privileges on referenced objects can be revoked and later procedure calls can fail due to lack of permission.

If you grant `EXECUTE` privilege as a default privilege at the database level, newly created procedures inherit that privilege from the database.

## Synopsis

![create\_proc syntax diagram](https://mintcdn.com/cockroachlabs/O8LdfYQ-dz7oGPVh/images/sql-diagrams/v24.2/create_proc.svg?fit=max\&auto=format\&n=O8LdfYQ-dz7oGPVh\&q=85\&s=3d384118ca255ed42e21a090ab4629c4)

## Parameters

| Parameter | Description |
| - | - |
| `routine_create_name` | The name of the procedure. |
| `routine_param` | A comma-separated list of procedure parameters, specifying the mode, name, and type. |
| `routine_body_str` | The body of the procedure. For allowed contents, see [Stored Procedures](https://docs.cockroachlabs.com/docs/v24.2/stored-procedures#structure). |

## Examples

The following are examples of basic stored procedures. For a more detailed example of a stored procedure, see [Create a stored procedure using PL/pgSQL](https://docs.cockroachlabs.com/docs/v24.2/stored-procedures).

### Create a stored procedure that uses a composite-type variable

Create a [composite variable](https://docs.cockroachlabs.com/docs/v24.2/create-type#create-a-composite-data-type):

```
CREATE TYPE comp AS (x INT, y STRING);
```

Create the procedure, declaring the `comp` variable you created:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE proc() LANGUAGE PLpgSQL AS $$
  DECLARE
    v comp := ROW(1, 'foo');
  BEGIN
    RAISE NOTICE '%', v;
  END
  $$;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CALL proc();
```

```
NOTICE: (1,foo)
CALL
```

### Create a stored procedure that uses `OUT` and `INOUT` parameters

The following example uses a combination of `OUT` and `INOUT` parameters to modify a provided value and output the result. An `OUT` parameter returns a value, while an `INOUT` parameter passes an input value and returns a value.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE double_triple(INOUT double INT, OUT triple INT) AS
  $$
  BEGIN
    double := double * 2;
    triple := double * 3;
  END;
  $$ LANGUAGE PLpgSQL;
```

When calling a procedure, you need to supply placeholder values for any `OUT` parameters. A `NULL` value is commonly used. When [calling a procedure from another routine](#create-a-stored-procedure-that-calls-a-procedure), you should declare variables that will store the results of the `OUT` parameters.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CALL double_triple(1, NULL);
```

```
  double | triple
---------+---------
       2 |      6
```

### Create a stored procedure that calls a procedure

The following example defines a procedure that calls the [`double_triple` example procedure](#create-a-stored-procedure-that-uses-out-and-inout-parameters). The `triple_result` variable is assigned the result of the `OUT` parameter, while the `double_input` variable both provides the input and stores the result of the `INOUT` parameter.

> [!NOTE]
>
> A procedure with `OUT` parameters can only be [called from a PL/pgSQL routine](https://docs.cockroachlabs.com/docs/v24.2/plpgsql#call-a-procedure).

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE p(double_input INT) AS
  $$
  DECLARE
    triple_result INT;
  BEGIN
    CALL double_triple(double_input, triple_result);
    RAISE NOTICE 'Doubled value: %', double_input;
    RAISE NOTICE 'Tripled value: %', triple_result;
  END
  $$ LANGUAGE PLpgSQL;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CALL p(1);
```

```
NOTICE: Doubled value: 2
NOTICE: Tripled value: 6
CALL
```

### Create a stored procedure that uses conditional logic

The following example uses [PL/pgSQL conditional statements](https://docs.cockroachlabs.com/docs/v24.2/plpgsql#write-conditional-statements):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE proc(a INT, b INT) AS
  $$
  DECLARE
    result INT;
  BEGIN
    IF a > b THEN
      RAISE NOTICE 'Condition met: a is greater than b';
    ELSE
      RAISE NOTICE 'Condition not met: a is not greater than b';
    END IF;
  END;
  $$ LANGUAGE PLpgSQL;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CALL proc(1, 2);
```

```
NOTICE: Condition not met: a is not greater than b
CALL
```

### Create a stored procedure that uses a `WHILE` loop

The following example uses [PL/pgSQL loop statements](https://docs.cockroachlabs.com/docs/v24.2/plpgsql#write-loops):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE OR REPLACE PROCEDURE arr_var() AS
  $$
  DECLARE
    x INT[] := ARRAY[1, 2, 3, 4, 5];
    n INT;
    i INT := 1;
  BEGIN
    n := array_length(x, 1);
    WHILE i <= n LOOP
      RAISE NOTICE '%: %', i, x[i];
      i := i + 1;
    END LOOP;
  END
  $$ LANGUAGE PLpgSQL;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CALL arr_var();
```

```
NOTICE: 1: 1
NOTICE: 2: 2
NOTICE: 3: 3
NOTICE: 4: 4
NOTICE: 5: 5
```

## See also

* [Stored Procedures](https://docs.cockroachlabs.com/docs/v24.2/stored-procedures)
* [PL/pgSQL](https://docs.cockroachlabs.com/docs/v24.2/plpgsql)
* [`CALL`](https://docs.cockroachlabs.com/docs/v24.2/call)
* [`ALTER PROCEDURE`](https://docs.cockroachlabs.com/docs/v24.2/alter-procedure)
* [`DROP PROCEDURE`](https://docs.cockroachlabs.com/docs/v24.2/drop-procedure)