# Stored Procedures

* They are compiled objects
* The procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
* Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
* Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
* Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
* Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
* An exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
* We can use Transactions in Procedure whereas we can't use Transactions in Function.

#### Sample Data

```sql
CREATE TABLE t_accounts (
    recid SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    balance dec(15,2) NOT NULL
);

drop table t_accounts;

INSERT INTO t_accounts (name,balance) values ('Adam',100),('Linda',100);

select * from t_accounts;
```

## Creating Procedure

```sql
CREATE OR REPLACE PROCEDURE pr_money_transfer 
    (sender int, receiver int, amount dec) 
AS
    $$

        BEGIN

            UPDATE t_accounts
            SET balance = balance - amount
            WHERE recid = sender;

            UPDATE t_accounts
            SET balance = balance + amount
            WHERE recid = receiver;

            COMMIT;

        END;
    $$
LANGUAGE PLPGSQL;

CALL pr_money_transfer(1,2,30);

select * from t_accounts;
```

## Another Example

```sql
CREATE OR REPLACE PROCEDURE pr_orders_count(INOUT total_count INTEGER DEFAULT 0 ) AS
$$
    BEGIN

        SELECT COUNT(*) INTO total_count FROM orders;

    END;
$$ LANGUAGE PLPGSQL;

CALL pr_orders_count();
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://dev117uday.gitbook.io/databases/sql/functions/stored-procedures.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.