SQL Stored Procedures

A Stored Procedure in SQL is a precompiled set of SQL statements stored in the database. Instead of writing and executing SQL queries repeatedly, you can store them as a procedure and call them whenever needed.

It is like a function in programming languages but for databases. Once created, you can execute it by just calling its name.

Key Features of Stored Procedures

• Precompiled – The procedure is compiled once and stored, making execution faster.
• Reusable – Write once, use multiple times.
• Secure – You can control which users can execute the procedure without giving them direct access to the underlying tables.
• Parameter Support – Accepts input values and can return output values.

Syntax of Stored Procedure

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL statements
END;

In MySQL, the syntax is slightly different:

DELIMITER //

CREATE PROCEDURE procedure_name()
BEGIN
    -- SQL statements
END //

DELIMITER ;

Example 1: Simple Stored Procedure

DELIMITER //

CREATE PROCEDURE GetAllStudents()
BEGIN
    SELECT * FROM students;
END //

DELIMITER ;

Execution:

CALL GetAllStudents();

Example 2: Stored Procedure with Parameters

DELIMITER //

CREATE PROCEDURE GetStudentByID(IN student_id INT)
BEGIN
    SELECT * FROM students WHERE id = student_id;
END //

DELIMITER ;

Execution:

CALL GetStudentByID(3);

Here:

• IN – Input parameter (value passed when calling).
• OUT – Output parameter (value returned).
• INOUT – Can be used for both input and output.

Advantages of Stored Procedures

1. Improved Performance
   • Stored procedures are compiled once and run faster than writing SQL every time.
2. Code Reusability
   • No need to rewrite queries; just call the procedure.
3. Security
   • Restrict users to run only specific procedures instead of giving direct table access.
4. Reduced Network Traffic
   • Instead of sending multiple SQL queries, a single call to the stored procedure can handle the work.
5. Maintainability
   • If a query changes, update it in one place (procedure), not in every application code.

Disadvantages of Stored Procedures

1. Complex Debugging
   • Troubleshooting can be harder than debugging application code.
2. Database Dependency
   • Logic is stored inside the database, making migration to another DBMS more difficult.
3. Version Control Issues
   • Keeping track of procedure versions can be challenging without proper tools.

When to Use Stored Procedures

• Repeatedly executed queries (like monthly reports).
• Complex business logic that runs entirely on the database.
• Data security and controlled access requirements.
• Performance optimization for large queries.

In short: A Stored Procedure in SQL is a powerful tool to store, secure, and reuse queries efficiently. It helps in improving performance, reducing repetitive code, and keeping your database operations organized.