---
title: "Stored procedures"
url: "https://docs.yugabyte.com/stable/explore/ysql-language-features/advanced-features/stored-procedures/"
---

# Stored procedures

Stored procedures in YSQL

This section describes how to use stored procedures to perform transactions.

## Create a stored procedure

Stored procedures, in large part, are just functions that support transactions. To create a stored procedure in YSQL, use the [CREATE PROCEDURE](/stable/api/ysql/the-sql-language/statements/ddl_create_procedure/ "CREATE PROCEDURE") statement, which has the following syntax:

```sql
CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE SQL
AS $$
DECLARE
-- variable declaration
BEGIN
-- stored procedure body
END;
$$;
```

Using return

Stored procedures don't return any values, other than errors. In a function, you use `RETURN <expression>` to return a value. In a stored procedure, RETURN does not support an expression, and ends the procedure immediately.

To return a value from a stored procedure, use an [INOUT](/stable/api/ysql/keywords "INOUT") parameter.

## Invoke a stored procedure

To invoke a stored procedure, use the [CALL](/stable/api/ysql/the-sql-language/statements/cmd_call/ "CALL") statement, which has the following syntax:

```sql
CALL stored_procedure_name(argument_list)
```

For example, drawing from the [Example workflow](#example-workflow "Example workflow") on this page:

```sql
yugabyte=# call move_money(1,2,1000);
```

## Delete a stored procedure

To remove a stored procedure, use the [DROP PROCEDURE](/stable/api/ysql/the-sql-language/statements/ddl_drop_procedure/ "DROP PROCEDURE") statement, which has the following syntax:

```sql
DROP PROCEDURE [IF EXISTS] stored_procedure_name(argument_list)
    [ CASCADE | RESTRICT ]
```

For example,

```sql
yugabyte=# drop procedure move_money(integer, integer, decimal);
```

If the name of the stored procedure is not unique (for example, if you had two `insert_data()` procedures, one of which accepted two integers and another which accepted an integer and a varchar), you must specify the data types in the DROP PROCEDURE statement. Otherwise, you can omit the data types.

## Example workflow

Before you start

The examples will run on any YugabyteDB universe.  
To create and connect to a universe, see [Set up YugabyteDB universe](/stable/explore/cluster-setup-local/).

In the following example, you create a new table and a stored procedure to perform operations on that table. Finally, you clean up by removing the procedure and the table.

1. Create an `accounts` table with two users, and set the balance of both accounts to $10,000:
   
   ```sql
   drop table if exists accounts;
   
   create table accounts (
     id int generated by default as identity,
     name varchar(100) not null,
     balance dec(15,2) not null,
     primary key(id)
   );
   
   insert into accounts(name,balance)
   values('User1',10000);
   
   insert into accounts(name,balance)
   values('User2',10000);
   ```
   
   Make sure the creation and insertions worked:
   
   ```sql
   select * from accounts;
   ```
   
   ```output
   id | name  | balance
   ----+-------+----------
     1 | User1 | 20000.00
     2 | User2 | 20000.00
   (2 rows)
   ```
2. Create a stored procedure to move money from one account to another:
   
   ```sql
   create or replace procedure move_money(
     origin integer,
     destination integer,
     amount decimal
   )
   language plpgsql
   as $$
   begin
       -- subtracting the amount from the origin account
       update accounts
       set balance = balance - amount
       where id = origin;
   
       -- adding the amount to the destination account
       update accounts
       set balance = balance + amount
       where id = destination;
   
       commit;
   end;$$;
   ```
3. Move $1000 from the first account to the second, then make sure it worked:
   
   ```sql
   call move_money(1,2,1000);
   ```
   
   ```sql
   select * from accounts;
   ```
   
   ```output
   id | name  | balance
   ----+-------+----------
     1 | User1 | 19000.00
     2 | User2 | 21000.00
   (2 rows)
   ```
4. Finally, clean up by removing the stored procedure and table:
   
   ```sql
   drop procedure if exists move_money;
   ```
   
   ```sql
   drop table if exists accounts;
   ```