Stored procedures are a powerful feature in MySQL that allows you to store SQL statements for later execution. They are often used to encapsulate business logic, improve code organization, and enhance security. This guide will help you understand stored procedures in MySQL, including their syntax, creation, execution, and examples.

1. What is a Stored Procedure?

A stored procedure is a precompiled and reusable set of SQL statements that are stored on the database server. It can be executed multiple times with different inputs, making it a handy tool for database management and application development.

2. Creating Stored Procedures

To create a stored procedure in MySQL, you use the CREATE PROCEDURE statement. Here’s a basic syntax:

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

• DELIMITER is used to specify a different delimiter (e.g., //) to avoid conflicts with the usual ; used to end statements.
• procedure_name is the name you choose for your stored procedure.
• BEGIN and END enclose the SQL statements that define the procedure’s functionality.

3. Executing Stored Procedures

You can execute a stored procedure using the CALL statement:

CALL procedure_name();

4. Parameters in Stored Procedures

Stored procedures can accept parameters, making them more versatile. To define parameters:

CREATE PROCEDURE procedure_name(param1 INT, param2 VARCHAR(255))
BEGIN
    -- SQL statements using parameters
END;

• param1 and param2 are placeholders for the parameters.
• Use these parameters within your SQL statements as needed.

5. Examples of Stored Procedures

Basic Example

Let’s create a simple stored procedure that retrieves all records from a table:

DELIMITER //
CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT * FROM employees;
END //
DELIMITER ;

Parameterized Example

Now, let’s create a stored procedure that accepts an employee ID and returns the corresponding employee’s information:

DELIMITER //
CREATE PROCEDURE GetEmployeeByID(IN emp_id INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = emp_id;
END //
DELIMITER ;

Conditional Logic Example

This example demonstrates using conditional logic in a stored procedure. It returns employees with salaries above a specified threshold:

DELIMITER //
CREATE PROCEDURE GetHighPaidEmployees(IN min_salary DECIMAL(10, 2))
BEGIN
    SELECT * FROM employees WHERE salary >= min_salary;
END //
DELIMITER ;

6. Altering and Dropping Stored Procedures

To alter a stored procedure, you can use the ALTER PROCEDURE statement with the modified code. To drop (delete) a stored procedure, use DROP PROCEDURE:

DROP PROCEDURE procedure_name;

7. Security Considerations

• Be cautious with user input in parameterized stored procedures to prevent SQL injection attacks.
• Restrict permissions for executing or modifying stored procedures to authorized users only.

8. Conclusion

Stored procedures are a valuable feature in MySQL for encapsulating SQL logic, enhancing code organization, and improving database security. Understanding how to create, execute, and work with parameters in stored procedures will help you better manage your MySQL databases and develop more efficient applications.