What are Stored Procedures in SQL?

✅ A Stored Procedure is:

A saved collection of SQL statements (plus optional logic like loops, conditions) that you can execute later by calling its name.

• Think of it like a function in programming — but for your database.
• You write it once and reuse it many times.
• Stored inside the database — not in your app code.
• Can have input parameters, output parameters, and return values.

Why Use Stored Procedures?

• ✅ Reuse code — no repeating the same SQL.
• ✅ Encapsulate logic — database workflows, business rules.
• ✅ Improve performance — precompiled, cached by the database.
• ✅ Improve security — control what users can execute without giving table access.

---

Basic Example

Imagine you want to get all users from a specific city.

Without stored procedure:

SELECT * FROM Users WHERE city = 'New York';

You write this everywhere you need it.

---

With a Stored Procedure:

CREATE PROCEDURE GetUsersByCity(IN city_name VARCHAR(100))
BEGIN
    SELECT * FROM Users WHERE city = city_name;
END;

Now to call it:

CALL GetUsersByCity('New York');

What Can Stored Procedures Do?

Feature	Description
Accept parameters	Input/output values — dynamic SQL.
Contain control logic	IF, CASE, WHILE, loops — not just plain SQL.
Perform multiple steps	Insert, Update, Delete — all in one procedure.
Return results	Output data sets or variables.
Error handling	TRY…CATCH (depends on DB).

---

Stored Procedure Syntax Overview

(Example in MySQL — syntax varies slightly in SQL Server, PostgreSQL, Oracle.)

CREATE PROCEDURE ProcedureName(IN param1 DATATYPE, OUT param2 DATATYPE)
BEGIN
    -- SQL statements here
END;

IN: Input parameter (you pass a value in).

OUT: Output parameter (procedure sets a value).

INOUT: Can do both.

Real-World Use Cases

Scenario	Stored Procedure Purpose
User registration	Validate, insert, send welcome message.
Monthly reports	Collect and process data from different tables.
Batch updates	Update many rows based on complex rules.
Transactions	Execute multiple steps with commit/rollback logic.

Stored Procedure vs Functions

Stored Procedure	Function
Can return zero or multiple results.	Must return a single value (scalar or table).
Can have complex logic, transactions.	Usually one calculation, no transactions.
Called with CALL or EXEC keyword.	Used inside SELECT queries.

In Short

Stored Procedures are saved programs inside the database — they bundle SQL logic into reusable, callable blocks.

✅ They make SQL modular, reusable, faster, and safer.