This guide explains how to use MySQL on Infomaniak hosting, in particular how stored procedures work.

Introduction

• “Stored procedures” and “stored routines” are not available on a shared web hosting plan.

Understanding stored procedures and routines

If stored procedures are essential to your project and you are currently using shared hosting, it is advisable to consider a VPS or a dedicated server, which offer more control and resources.

Stored procedures are an effective way to automate tasks and integrate business logic directly into the database. This allows for more efficient and easier-to-maintain applications.

On a Cloud Server, as long as the user has administrator rights on the relevant MySQL database, they have the necessary permissions to execute SQL statements, including the EXECUTE command, which is used to launch stored procedures that are already present in the database.

The user also has the required privileges to create new stored procedures.A stored procedure is created using a specific SQL syntax that defines the instructions to be executed, followed by saving it in the database.

Example

DELIMITER //
CREATE PROCEDURE GetUserCount()
BEGIN
    SELECT COUNT(*) AS total_users FROM users;
    -- Returns the total number of users in the table
END //
DELIMITER ;

-- Execute the stored procedure
CALL GetUserCount();