# Stored Procedures

A **Stored Procedure** is a prepared SQL code that you can save, so the code can be reused over and over again. It is a subroutine available to applications that access a relational database management system (RDBMS).

## 1. The Core Concept

Instead of writing complex SQL queries inside your application code (e.g., Java, Python, Node.js) and sending them to the database every time, you define the logic *inside* the database itself.

*   **Precompiled:** The database parses, compiles, and optimizes the query plan once and stores it. Subsequent calls are faster.
*   **Parameters:** Procedures can accept input parameters and return output parameters.

## 2. Example (SQL)

Creating a procedure to get all customers from a specific city:

```sql
CREATE PROCEDURE GetCustomersByCity (IN cityName VARCHAR(50))
BEGIN
    SELECT * 
    FROM Customers 
    WHERE City = cityName;
END;
```

Calling the procedure:

```sql
CALL GetCustomersByCity('London');
```

## 3. Benefits

1.  **Performance:** Since the SQL is precompiled, the execution is often faster than sending raw SQL strings.
2.  **Reduced Network Traffic:** Instead of sending a 50-line SQL query over the network, you send a short command (`CALL MyProc`).
3.  **Security:** You can grant users permission to execute a procedure without granting them permission to access the underlying tables directly. This prevents SQL Injection if implemented correctly (using parameters).
4.  **Centralized Logic:** Business logic involving data is kept in one place (the DB). If the logic changes, you update the procedure, not 10 different microservices.

## 4. Drawbacks

*   **Vendor Lock-in:** Stored procedure syntax (PL/SQL for Oracle, T-SQL for SQL Server, PL/pgSQL for Postgres) varies significantly between databases. Moving from Oracle to Postgres is painful.
*   **Debugging:** Debugging stored procedures is generally harder than debugging application code.
*   **Version Control:** It is harder to track changes to stored procedures in Git compared to application code files.
*   **Resource Usage:** Puts more CPU/Memory load on the database server (which is harder to scale) rather than the application server (which is easier to scale).

## 5. SQL Injection and Stored Procedures

Stored procedures are often cited as a defense against SQL Injection, but they are not a silver bullet.

*   **Safe:** When using parameters (as in the example above), the database treats the input strictly as data, not executable code. This effectively neutralizes injection attacks.
*   **Unsafe (Dynamic SQL):** If you concatenate strings *inside* the stored procedure to build a query dynamically, you are still vulnerable.

### Vulnerable Example (Dynamic SQL)
```sql
CREATE PROCEDURE UnsafeSearch (IN nameInput VARCHAR(50))
BEGIN
    -- Concatenating input directly into the query string
    SET @s = CONCAT('SELECT * FROM Users WHERE name = "', nameInput, '"');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
END;
```
If `nameInput` is `"; DROP TABLE Users; --`, the procedure executes the drop command. Always use parameterized queries, even inside stored procedures.

[[programming/database-basics]]
[[programming/database-views]]
[[programming/database-transactions]]