> ## Documentation Index
> Fetch the complete documentation index at: https://www.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.
# CREATE PROCEDURE
export const InlineImage = ({src, alt = "", height = "1.6em"}) => {
return 
;
};
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
New in v23.2: The `CREATE PROCEDURE` statement defines a stored procedure.
## Required privileges
* To define a procedure, a user must have `CREATE` privilege on the schema of the procedure.
* To define a procedure with a user-defined type, a user must have `USAGE` privilege on the user-defined type.
* To resolve a procedure, a user must have at least the `USAGE` privilege on the schema of the procedure.
* To call a procedure, a user must have `EXECUTE` privilege 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
## Parameters
| Parameter | Description |
| ----------------------- | --------------------------------------------------------------------------------------------------------------------------------------- |
| `routine\_create\_name` | The name of the procedure. |
| `routine\_param` | A comma-separated list of procedure parameters. |
| `routine\_body\_str` | The body of the procedure. For allowed contents, see Stored Procedures. |
## 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.
### Create a stored procedure that uses a composite-type variable
Create a composite variable:
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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();
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
NOTICE: (1,foo)
CALL
```
### Create a stored procedure that uses conditional logic
The following example uses PL/pgSQL 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);
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```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();
```
```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
NOTICE: 1: 1
NOTICE: 2: 2
NOTICE: 3: 3
NOTICE: 4: 4
NOTICE: 5: 5
```
## See also
* Stored Procedures
* PL/pgSQL
* `CALL`
* `ALTER PROCEDURE`
* `DROP PROCEDURE`