This guide presents the use of MySQL on Infomaniak hosting, particularly the operation of stored procedures.

Preamble

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

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 results in more performant and easier-to-maintain applications.

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

The user also has the required privileges to create new stored procedures. Creating a stored procedure is done via specific SQL syntax defining the instructions to execute, followed by its recording 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();