Using MySQL Stored Procedure

What Will You Learn in This Guide?

• MySQL Stored Procedure concept
• Creating stored procedures without and with parameters
• Retrieving and returning data with IN and OUT parameters
• Executing and deleting stored procedures

Technical Analysis and Summary

This guide covers stored procedures used in a MySQL database to manage business logic at the database level.
The goal is to reduce repetitive SQL queries in application code and increase security.
Steps; The sample consists of database preparation, creating a stored procedure, working with parameters, and deleting the procedure.

---

1. Sample Database and Table Preparation

First of all, a database is created for testing operations and a table to hold vehicle information.

The following command creates and activates a new database:

CREATE DATABASE genixnode_db;
USE genixnode_db;

• This command creates the table that will store vehicle information:

---

CREATE TABLE cars (
    make varchar(100),
    model varchar(100),
    year int,
    value decimal(10, 2)
);

---

To add sample data to the table:

INSERT INTO cars VALUES
('Porsche', '911 GT3', 2020, 169700),
('Ferrari', '488 GTB', 2015, 254750);

---

2. Creating a Stored Procedure Without Parameters

1. A frequently used query can be reused by turning it into a stored procedure.

• The following code creates a routine that sorts all vehicles by brand and value:

DELIMITER //
CREATE PROCEDURE get_all_cars()
BEGIN
    SELECT * FROM cars ORDER BY make, value DESC;

END //
DELIMITER ;

---

• To run the stored procedure:

CALL get_all_cars();

---

3. Input (IN) Parameter Usage

1. Stored procedures can be created that filter with a value received from the user.

The following procedure lists vehicles by year of manufacture:

DELIMITER //
CREATE PROCEDURE get_cars_by_year(IN year_filter int)
BEGIN
    SELECT * FROM cars WHERE year = year_filter;
END //
DELIMITER ;

---

• To bring 2017 model vehicles:

CALL get_cars_by_year(2017);

---

4. Getting Results with Output Parameter

1. In some cases the stored procedure needs to return more than one value.

• The following procedure returns the number of vehicles and the highest value for a given year:

DELIMITER //
CREATE PROCEDURE get_car_stats(
    IN year_filter int,
    OUT car_count int,
    OUT max_val decimal(10,2)
)
BEGIN
    SELECT COUNT(*), MAX(value)
    INTO car_count, max_val
    FROM cars
    WHERE year = year_filter;
END //
DELIMITER ;

---

• Variables are used to get the results:

CALL get_car_stats(2020, @count, @max);
SELECT @count, @max;

---

5. Delete Stored Procedure

1. If a stored procedure is to be updated, it must be deleted first.

• The following command removes the specified stored procedure from the database:

DROP PROCEDURE IF EXISTS get_all_cars;

---

Frequently Asked Questions (FAQ)

1. Does using a stored procedure increase performance? Yes, network traffic is reduced because queries run on the server side.

2. Why is the use of DELIMITER necessary? It prevents semicolons within the procedure from terminating the query prematurely.

3. Can a stored procedure call another stored procedure? Yes, it is possible with the CALL command.

4. What privileges are required? CREATE ROUTINE and EXECUTE privileges are required.

---

Result

• MySQL stored procedures make database operations more streamlined, secure and maintainable.
• With this guide, you can use stored procedures with and without parameters and manage business logic at the database level.
• You can make your projects more scalable by applying this structure in high-performance GenixNode infrastructures.