How to Create a Stored Procedure in MySQL with Multiple Parameters?

Creating a stored procedure in MySQL with multiple parameters is straightforward. You can define the procedure with various input parameters and use them within the procedure's logic.

Here's a step-by-step guide:

1. Define the Stored Procedure

Begin by using the CREATE PROCEDURE statement. This statement takes the procedure name and defines the input parameters using the IN keyword. Each parameter requires a data type.

CREATE PROCEDURE my_procedure (IN param1 INT, IN param2 VARCHAR(255))
BEGIN
  -- Procedure logic goes here
END;

2. Write the Procedure Logic

Inside the BEGIN...END block, write the SQL statements that define the procedure's functionality. These statements can access and manipulate the input parameters.

CREATE PROCEDURE my_procedure (IN param1 INT, IN param2 VARCHAR(255))
BEGIN
  -- Example: Select data based on input parameters
  SELECT * FROM my_table WHERE id = param1 AND name = param2;
END;

3. Call the Stored Procedure

After creating the procedure, you can call it using the CALL statement, passing the desired values for the input parameters.

CALL my_procedure(10, 'John Doe');

Example:

Let's create a stored procedure to calculate the total price of a product based on its quantity and price per unit:

CREATE PROCEDURE calculate_total_price (IN quantity INT, IN price_per_unit DECIMAL(10,2))
BEGIN
  DECLARE total_price DECIMAL(10,2);
  SET total_price = quantity * price_per_unit;
  SELECT total_price;
END;

Now, you can call this procedure with the desired quantity and price per unit:

CALL calculate_total_price(5, 10.99);

This will output the total price (55.95) based on the provided inputs.

Key Points:

Use IN keyword for input parameters.
Define data types for each parameter.
Access parameters within the procedure logic.
Call the procedure using the CALL statement with input values.

By following these steps, you can effectively create and use stored procedures with multiple parameters in MySQL.

A2oz