MySQL Parameters in Stored Procedures
When creating stored procedures in MySQL, parameters allow you to make your procedures more flexible and reusable. Parameters act as variables that receive values when the stored procedure is called, enabling you to write dynamic procedures that can operate on different data each time they're executed.
Introduction to Parameters
Parameters in MySQL stored procedures are similar to function parameters in programming languages. They provide a way to pass values into a procedure, process those values, and potentially return results. This approach makes your database code more modular and maintainable.
Let's understand the basics of how parameters work in MySQL stored procedures.
Parameter Syntax
In MySQL, you define parameters within the parentheses after the procedure name in the CREATE PROCEDURE statement. The basic syntax is:
CREATE PROCEDURE procedure_name(
parameter_mode parameter_name data_type,
parameter_mode parameter_name data_type,
...
)
BEGIN
-- Procedure body
END;
Where:
procedure_nameis the name you give to your stored procedureparameter_modespecifies how the parameter is used (IN, OUT, or INOUT)parameter_nameis the name of the parameter (like a variable name)data_typeis any valid MySQL data type
Parameter Modes
MySQL supports three parameter modes:
- IN parameters: Used to pass values into a procedure (default mode)
- OUT parameters: Used to return values from a procedure
- INOUT parameters: Used for both input and output
Let's examine each mode in detail.
IN Parameters
IN parameters are the most common type. They pass values into the procedure but cannot return values. Any changes made to an IN parameter inside the procedure are not visible outside the procedure.
Example:
DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(
IN dept_name VARCHAR(50)
)
BEGIN
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE department = dept_name;
END //
DELIMITER ;
Usage:
CALL GetEmployeesByDepartment('Marketing');
Output:
+------------+------------+-----------+------------+
| employee_id | first_name | last_name | hire_date |
+------------+------------+-----------+------------+
| 103 | Sarah | Johnson | 2018-05-21 |
| 107 | Michael | Williams | 2019-11-15 |
| 112 | Jennifer | Davis | 2020-03-08 |
+------------+------------+-----------+------------+
OUT Parameters
OUT parameters allow the procedure to return values to the caller. When entering the procedure, OUT parameters are NULL, and their final values are returned to the caller when the procedure completes.
Example:
DELIMITER //
CREATE PROCEDURE GetEmployeeCount(
IN dept_name VARCHAR(50),
OUT total_count INT
)
BEGIN
SELECT COUNT(*) INTO total_count
FROM employees
WHERE department = dept_name;
END //
DELIMITER ;
Usage:
SET @count = 0;
CALL GetEmployeeCount('Marketing', @count);
SELECT @count AS 'Number of Employees in Marketing';
Output:
+----------------------------------+
| Number of Employees in Marketing |
+----------------------------------+
| 3 |
+----------------------------------+
INOUT Parameters
INOUT parameters combine the features of IN and OUT parameters. They pass values into the procedure and can also return values. The initial value of an INOUT parameter can be changed inside the procedure, and the final value is returned to the caller.
Example:
DELIMITER //
CREATE PROCEDURE AdjustSalary(
INOUT employee_salary DECIMAL(10,2),
IN increase_percentage DECIMAL(5,2)
)
BEGIN
SET employee_salary = employee_salary * (1 + increase_percentage/100);
END //
DELIMITER ;
Usage:
SET @current_salary = 5000.00;
CALL AdjustSalary(@current_salary, 10);
SELECT @current_salary AS 'New Salary';
Output:
+------------+
| New Salary |
+------------+
| 5500.00 |
+------------+
Default Parameter Values
MySQL doesn't directly support default parameter values in stored procedure definitions. However, you can simulate this behavior by checking if a parameter is NULL and assigning a default value within the procedure body.
Example:
DELIMITER //
CREATE PROCEDURE GetProducts(
IN min_price DECIMAL(10,2),
IN max_price DECIMAL(10,2)
)
BEGIN
-- Set default values if parameters are NULL
IF min_price IS NULL THEN
SET min_price = 0;
END IF;
IF max_price IS NULL THEN
SET max_price = 1000;
END IF;
-- Use the parameters
SELECT product_id, product_name, price
FROM products
WHERE price BETWEEN min_price AND max_price;
END //
DELIMITER ;
Usage:
-- Using default values
CALL GetProducts(NULL, NULL);
-- Specifying min_price only
CALL GetProducts(50, NULL);
-- Specifying both parameters
CALL GetProducts(50, 200);
Named Parameters
When calling stored procedures, MySQL allows you to use either positional parameters or named parameters.
Example with positional parameters:
CALL GetEmployeesByDepartment('Marketing');
Example with named parameters:
CALL GetEmployeesByDepartment(dept_name => 'Marketing');
Using named parameters makes your code more readable, especially when dealing with multiple parameters.
Practical Examples
Let's explore some practical examples of parameters in stored procedures that you might use in real-world applications.
Example 1: User Registration Procedure
DELIMITER //
CREATE PROCEDURE RegisterUser(
IN p_username VARCHAR(50),
IN p_email VARCHAR(100),
IN p_password VARCHAR(255),
OUT p_success BOOLEAN,
OUT p_message VARCHAR(100)
)
BEGIN
DECLARE email_exists INT DEFAULT 0;
-- Check if email already exists
SELECT COUNT(*) INTO email_exists FROM users WHERE email = p_email;
IF email_exists > 0 THEN
SET p_success = FALSE;
SET p_message = 'Email already registered';
ELSE
-- Insert new user
INSERT INTO users (username, email, password, created_at)
VALUES (p_username, p_email, p_password, NOW());
SET p_success = TRUE;
SET p_message = 'Registration successful';
END IF;
END //
DELIMITER ;
Usage:
SET @success = FALSE;
SET @message = '';
CALL RegisterUser('johndoe', '[email protected]', 'hashed_password', @success, @message);
SELECT @success, @message;
Example 2: Order Processing Procedure
DELIMITER //
CREATE PROCEDURE ProcessOrder(
IN p_customer_id INT,
IN p_product_id INT,
IN p_quantity INT,
OUT p_order_id INT,
OUT p_status VARCHAR(50)
)
BEGIN
DECLARE product_stock INT;
DECLARE product_price DECIMAL(10,2);
-- Check stock availability
SELECT stock, price INTO product_stock, product_price
FROM products
WHERE product_id = p_product_id;
IF product_stock < p_quantity THEN
SET p_status = 'Insufficient stock';
SET p_order_id = NULL;
ELSE
-- Insert order
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (p_customer_id, CURDATE(), p_quantity * product_price);
-- Get the generated order ID
SET p_order_id = LAST_INSERT_ID();
-- Insert order details
INSERT INTO order_details (order_id, product_id, quantity, price)
VALUES (p_order_id, p_product_id, p_quantity, product_price);
-- Update stock
UPDATE products
SET stock = stock - p_quantity
WHERE product_id = p_product_id;
SET p_status = 'Order processed successfully';
END IF;
END //
DELIMITER ;
Usage:
SET @order_id = 0;
SET @status = '';
CALL ProcessOrder(101, 5, 2, @order_id, @status);
SELECT @order_id AS 'Order ID', @status AS 'Status';
Example 3: Report Generation with Multiple Parameters
DELIMITER //
CREATE PROCEDURE GenerateSalesReport(
IN p_start_date DATE,
IN p_end_date DATE,
IN p_product_category VARCHAR(50),
IN p_include_returns BOOLEAN
)
BEGIN
SELECT
p.product_id,
p.product_name,
p.category,
SUM(od.quantity) AS units_sold,
SUM(od.quantity * od.price) AS total_sales
FROM
orders o
JOIN
order_details od ON o.order_id = od.order_id
JOIN
products p ON od.product_id = p.product_id
WHERE
o.order_date BETWEEN p_start_date AND p_end_date
AND (p_product_category IS NULL OR p.category = p_product_category)
AND (p_include_returns = TRUE OR o.order_status != 'Returned')
GROUP BY
p.product_id, p.product_name, p.category
ORDER BY
total_sales DESC;
END //
DELIMITER ;
Usage:
-- Generate report for all categories in January 2023, excluding returns
CALL GenerateSalesReport('2023-01-01', '2023-01-31', NULL, FALSE);
-- Generate report for Electronics category in 2022, including returns
CALL GenerateSalesReport('2022-01-01', '2022-12-31', 'Electronics', TRUE);
Best Practices for Using Parameters
To make your stored procedures more reliable and easier to maintain, follow these best practices:
-
Use clear parameter naming: Prefix parameters with
p_to distinguish them from table columns.sqlCREATE PROCEDURE GetEmployee(IN p_employee_id INT) -
Validate input parameters: Always check parameters for NULL or invalid values.
sqlIF p_employee_id IS NULL OR p_employee_id <= 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid employee ID';
END IF; -
Document your parameters: Add comments to explain each parameter's purpose.
sqlCREATE PROCEDURE CalculateTax(
IN p_income DECIMAL(10,2), -- Annual income in USD
IN p_tax_year INT, -- Tax year to calculate for
OUT p_tax_amount DECIMAL(10,2) -- Resulting tax amount
) -
Use the appropriate parameter mode: Choose IN, OUT, or INOUT based on how each parameter will be used.
-
Limit the number of parameters: If you need many parameters, consider grouping related ones using a table structure.
Common Errors with Parameters
When working with parameters in stored procedures, you might encounter these common issues:
-
Forgetting to specify parameter mode: If you omit the mode, MySQL defaults to IN.
-
Using the wrong data type: Ensure parameter data types match the data you'll be working with.
-
Not initializing OUT parameters: When calling a procedure, OUT parameters must be initialized before use.
sqlSET @result = 0; -- Initialize before calling
CALL MyProcedure(@result); -
Confusing IN and OUT modes: Remember that changes to IN parameters aren't visible outside the procedure.
Summary
Parameters in MySQL stored procedures provide a powerful way to create flexible, reusable database code. By understanding the different parameter modes (IN, OUT, and INOUT) and implementing best practices, you can create efficient and maintainable stored procedures for your applications.
To recap:
- IN parameters pass values into procedures
- OUT parameters return values from procedures
- INOUT parameters both receive and return values
- Parameters make your procedures more dynamic and reusable
- Proper parameter naming and validation improve code quality
Exercises
-
Create a stored procedure that accepts a customer ID and returns the total number of orders and the total amount spent by that customer.
-
Modify the
RegisterUserprocedure to include additional parameters for first name, last name, and date of birth. -
Create a stored procedure that transfers money between two bank accounts, using parameters for account IDs and the transfer amount. Include appropriate validation.
-
Build a reporting procedure that accepts multiple filter parameters (date range, category, status) and returns filtered data from a sales table.
-
Create a procedure with INOUT parameters that calculates compound interest on a principal amount over a specified period, with the initial principal passed in and the final amount returned.
Additional Resources
- MySQL Official Documentation on Stored Procedures
- MySQL Parameter Handling
- Advanced MySQL Stored Procedure Programming
By mastering parameters in MySQL stored procedures, you'll be better equipped to write efficient, reusable database code that integrates smoothly with your applications.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)