SQL Stored Procedures

Overview

A Stored Procedure is a set of SQL statements that can be stored and executed in the database server. They allow you to encapsulate business logic, making your database operations more efficient and maintainable.

Advantages of Stored Procedures

• Improved performance by reducing network traffic.
• Encapsulation of business logic for better maintenance.
• Enhanced security through permission controls.
• Reusability of code across multiple applications.

Creating a Stored Procedure

The basic syntax for creating a Stored Procedure is as follows:

CREATE PROCEDURE procedure_name (parameters)
BEGIN
    -- SQL statements
END;

Example: Simple Stored Procedure

Here’s an example of a simple Stored Procedure that retrieves all employees from the Employees table:

CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM Employees;
END;

Calling a Stored Procedure

You can call a Stored Procedure using the CALL statement:

CALL GetAllEmployees();

Example: Stored Procedure with Parameters

Stored Procedures can also accept parameters. Here’s an example of a procedure that retrieves employees based on their department ID:

CREATE PROCEDURE GetEmployeesByDepartment(IN dept_id INT)
BEGIN
    SELECT * FROM Employees WHERE DepartmentID = dept_id;
END;

To call this procedure, you would use:

CALL GetEmployeesByDepartment(2);

Modifying a Stored Procedure

To modify an existing Stored Procedure, you can use the ALTER PROCEDURE statement or drop and recreate it:

DROP PROCEDURE IF EXISTS GetEmployeesByDepartment;

CREATE PROCEDURE GetEmployeesByDepartment(IN dept_id INT)
BEGIN
    SELECT * FROM Employees WHERE DepartmentID = dept_id;
END;

Deleting a Stored Procedure

To delete a Stored Procedure, use the DROP PROCEDURE statement:

DROP PROCEDURE GetAllEmployees;

Conclusion

SQL Stored Procedures are a powerful tool for encapsulating SQL logic and improving performance and security in database applications. By utilizing Stored Procedures, developers can create reusable and efficient code to handle complex database operations.