Stored Procedures in MySql

A stored procedure is a reusable SQL code that is stored in the database and can be executed with specific input parameters. Below is a basic example of how to create and call a stored procedure.

1.) Creating a Stored Procedure

Let’s create a stored procedure to fetch users by a given status.

DELIMITER //

CREATE PROCEDURE GetUsersByStatus (IN user_status VARCHAR(20))
BEGIN
    SELECT id, name, email
    FROM users
    WHERE status = user_status;
END //

DELIMITER ;

• DELIMITER //: Changes the default statement delimiter (;) to // so that the procedure body can contain semicolons without terminating prematurely.
• IN user_status VARCHAR(20): Defines an input parameter of type VARCHAR(20) named user_status.
• BEGIN ... END: Defines the body of the procedure, which contains the SQL logic.

2.) Calling the Stored Procedure

You can call the stored procedure using the CALL statement.

CALL GetUsersByStatus('active');

This will execute the stored procedure GetUsersByStatus with 'active' as the input parameter, and it will return all users with the status active.

3.) Example with Output Parameter

Stored procedures can also return values using output parameters.

DELIMITER //

CREATE PROCEDURE GetUserCount (IN user_status VARCHAR(20), OUT user_count INT)
BEGIN
    SELECT COUNT(*) INTO user_count
    FROM users
    WHERE status = user_status;
END //

DELIMITER ;

• OUT user_count INT: Defines an output parameter to store the result.

Call Procedure with Output:

SET @count = 0; -- Declare a variable to store the output
CALL GetUserCount('active', @count);
SELECT @count AS ActiveUsers; -- Display the result

• @count is a session variable that receives the output value from the procedure.

4.) Example with Both Input and Output Parameters

Create Procedure:

DELIMITER //

CREATE PROCEDURE CalculateSum (IN num1 INT, IN num2 INT, OUT sum_result INT)
BEGIN
    SET sum_result = num1 + num2;
END //

DELIMITER ;

Call Procedure:

SET @result = 0;
CALL CalculateSum(5, 10, @result);
SELECT @result AS SumResult;

This will return the sum of 5 and 10 as 15.

Notes:

1. Privileges: Ensure the user has sufficient privileges to create stored procedures (CREATE ROUTINE privilege).
2. Error Handling: Advanced procedures can include error handling with DECLARE and HANDLER statements.
3. Performance: Stored procedures execute on the database server, reducing network latency for complex queries.