MySQL Stored Procedure: How to Create & Examples 2026

When your application has complex SQL logic that is used repeatedly, a Stored Procedure is the solution. A Stored Procedure is a set of SQL statements stored inside the database that can be called at any time with a single command. This article covers how to create and use Stored Procedures in MySQL 8 in full.

Advantages of Using Stored Procedures

• Reusability: Write once, call many times from anywhere.
• Performance: Queries are compiled and stored on the server, so execution is faster.
• Security: Users can call a procedure without direct access to the tables.
• Separation of logic: Business logic can be stored in the database, not just in the application code.

Preparation: Changing the DELIMITER

Because the body of a stored procedure contains semicolons ;, we need to temporarily change the delimiter so that MySQL doesn't end the command prematurely:

-- Change the delimiter before creating the procedure
DELIMITER $$

-- After you're done, restore the delimiter to ;
DELIMITER ;

Creating a Simple Stored Procedure

Here is the most basic example: a procedure with no parameters that displays all products.

DELIMITER $$

CREATE PROCEDURE tampilkan_semua_produk()
BEGIN
    SELECT id, nama, kategori, harga, stok
    FROM produk
    ORDER BY nama;
END$$

DELIMITER ;

-- How to call the procedure
CALL tampilkan_semua_produk();

Stored Procedure with an IN Parameter

The IN parameter is used to pass a value into the procedure (read-only inside the procedure):

DELIMITER $$

CREATE PROCEDURE cari_produk_by_kategori(IN p_kategori VARCHAR(50))
BEGIN
    SELECT id, nama, harga, stok
    FROM produk
    WHERE kategori = p_kategori
    ORDER BY harga ASC;
END$$

DELIMITER ;

-- Call it with an argument
CALL cari_produk_by_kategori('Elektronik');
CALL cari_produk_by_kategori('Furnitur');

Stored Procedure with an OUT Parameter

The OUT parameter is used to return a value from the procedure back to the caller:

DELIMITER $$

CREATE PROCEDURE hitung_stok_total(
    IN p_kategori VARCHAR(50),
    OUT p_total_stok INT,
    OUT p_jumlah_produk INT
)
BEGIN
    SELECT
        COALESCE(SUM(stok), 0),
        COUNT(*)
    INTO p_total_stok, p_jumlah_produk
    FROM produk
    WHERE kategori = p_kategori;
END$$

DELIMITER ;

-- Call it and retrieve the results
CALL hitung_stok_total('Elektronik', @total_stok, @jumlah_produk);
SELECT @total_stok AS total_stok, @jumlah_produk AS jumlah_produk;

Stored Procedure with an INOUT Parameter

The INOUT parameter can be used to send a value into the procedure and receive a value back at the same time:

DELIMITER $$

CREATE PROCEDURE hitung_diskon(INOUT p_harga DECIMAL(10,2), IN p_persen INT)
BEGIN
    SET p_harga = p_harga - (p_harga * p_persen / 100);
END$$

DELIMITER ;

-- Use the INOUT parameter
SET @harga_asal = 1000000;
CALL hitung_diskon(@harga_asal, 15);
SELECT @harga_asal AS harga_setelah_diskon;

Stored Procedure with IF-ELSE Logic

Stored Procedures support control structures such as IF, ELSE, and CASE:

DELIMITER $$

CREATE PROCEDURE kategorikan_produk(IN p_harga DECIMAL(10,2), OUT p_kategori_harga VARCHAR(20))
BEGIN
    IF p_harga < 100000 THEN
        SET p_kategori_harga = 'Murah';
    ELSEIF p_harga < 1000000 THEN
        SET p_kategori_harga = 'Menengah';
    ELSE
        SET p_kategori_harga = 'Premium';
    END IF;
END$$

DELIMITER ;

CALL kategorikan_produk(750000, @kat);
SELECT @kat AS kategori_harga;

Stored Procedure with a Loop and a Transaction

DELIMITER $$

CREATE PROCEDURE update_harga_massal(IN p_kategori VARCHAR(50), IN p_persen_naik INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT;
    DECLARE v_harga DECIMAL(10,2);

    DECLARE cur CURSOR FOR
        SELECT id, harga FROM produk WHERE kategori = p_kategori;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    START TRANSACTION;

    OPEN cur;
    loop_label: LOOP
        FETCH cur INTO v_id, v_harga;
        IF done THEN
            LEAVE loop_label;
        END IF;
        UPDATE produk
        SET harga = v_harga + (v_harga * p_persen_naik / 100)
        WHERE id = v_id;
    END LOOP;
    CLOSE cur;

    COMMIT;
    SELECT ROW_COUNT() AS baris_diperbarui;
END$$

DELIMITER ;

Viewing and Dropping a Stored Procedure

-- View all stored procedures in the active database
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- View the procedure's source code
SHOW CREATE PROCEDURE cari_produk_by_kategori;

-- Drop the stored procedure
DROP PROCEDURE IF EXISTS cari_produk_by_kategori;

Conclusion

Stored Procedures are a powerful MySQL feature for storing and running complex SQL logic directly at the database level. With IN, OUT, and INOUT parameters, you can create flexible and reusable procedures. Use stored procedures for frequently repeated operations, complex business logic, or when securing data access is a priority. Always remember to change the DELIMITER before creating a procedure so that MySQL can process the code correctly.