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](../../../../api/ysql/the-sql-language/statements/ddl_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;
$$;
```

{{< note title="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](../../../../api/ysql/keywords) parameter.

{{< /note >}}

## Invoke a stored procedure

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

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

For example, drawing from the [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](../../../../api/ysql/the-sql-language/statements/ddl_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

{{% explore-setup-single-new %}}

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)
    ```

1. 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;$$;
    ```

1. 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)
    ```

1. Finally, clean up by removing the stored procedure and table:

    ```sql
    drop procedure if exists move_money;
    ```

    ```sql
    drop table if exists accounts;
    ```