SQL Stored Procedures

Stored Procedures are pre-written SQL code saved in the database that you can execute repeatedly. They help encapsulate logic, improve performance, and simplify complex tasks.

🔹 What is a Stored Procedure?
A stored procedure is a named set of SQL statements that can accept input parameters, perform operations, and optionally return results. It runs on the database server, reducing client-server communication.

🔹 Basic Syntax

-- MySQL / PostgreSQL / SQL Server syntax example
CREATE PROCEDURE procedure_name (IN param1 INT, OUT param2 VARCHAR(50))
BEGIN
    -- SQL statements here
END;

Note: Syntax varies slightly by DBMS (especially for parameter modes and delimiters).

🔹 Simple Example: Add Two Numbers

-- MySQL example
DELIMITER //
CREATE PROCEDURE AddNumbers(IN a INT, IN b INT, OUT sum INT)
BEGIN
    SET sum = a + b;
END //
DELIMITER ;

Call the procedure:

CALL AddNumbers(5, 7, @result);
SELECT @result;  -- Output: 12

🔹 Use Cases

• Encapsulate complex business logic
• Reuse code efficiently
• Improve performance by reducing network traffic
• Implement transactions with multiple SQL statements

🔹 Advantages

• Centralized logic in DB
• Enhanced security (control user access)
• Reduced client complexity
• Easier maintenance

🔹 Important Notes

• Stored procedures may differ syntactically between DBMS (MySQL, Oracle, SQL Server, PostgreSQL) — always check documentation.
• Use appropriate error handling within procedures.
• Avoid overusing stored procedures for simple queries where direct SQL suffices.

🧠 Quick Recap

💡 Stored procedures streamline SQL operations and help keep your application logic organized and efficient.