MySql - Using parameters with stored procedures and functions

Using Parameters with Stored Procedures and Functions in MySQL

Introduction

In MySQL, stored procedures and functions allow developers to encapsulate reusable SQL logic on the database server. One of their core features is the ability to accept parameters, enabling dynamic and flexible execution based on input values. Parameters provide a way to pass data into stored procedures and functions, and also return data back to the caller.

This article provides an in-depth exploration of using parameters with stored procedures and functions in MySQL. We will cover parameter types, syntax, usage patterns, best practices, and provide extensive examples to help you master this crucial feature.

Understanding Stored Procedures and Functions

Stored Procedures vs Functions

Parameter Types in MySQL Stored Procedures

MySQL supports three kinds of parameters in stored procedures:

• IN: Input parameter. Passes a value from the caller to the procedure. Default type if unspecified.
• OUT: Output parameter. Used by the procedure to send data back to the caller.
• INOUT: Input/output parameter. Passes an initial value to the procedure and returns a modified value.

Functions accept only IN parameters.

Syntax for Defining Parameters

CREATE PROCEDURE procedure_name (
    [IN|OUT|INOUT] param_name datatype,
    ...
)
BEGIN
    -- procedure body
END;

Using Parameters in Stored Procedures

IN Parameters

IN parameters are used to pass values into the procedure. Inside the procedure, these parameters act like constants and cannot be modified.

Example: IN Parameter

DELIMITER //

CREATE PROCEDURE GetEmployeesByDept(IN dept VARCHAR(50))
BEGIN
    SELECT employee_id, name, department
    FROM employees
    WHERE department = dept;
END //

DELIMITER ;

Execution:

CALL GetEmployeesByDept('Finance');

OUT Parameters

OUT parameters are used to return values from the procedure to the caller. The procedure assigns a value to the OUT parameter which can then be retrieved by the caller.

Example: OUT Parameter

DELIMITER //

CREATE PROCEDURE GetEmployeeCountByDept(
    IN dept VARCHAR(50),
    OUT emp_count INT
)
BEGIN
    SELECT COUNT(*) INTO emp_count
    FROM employees
    WHERE department = dept;
END //

DELIMITER ;

Execution and retrieval:

CALL GetEmployeeCountByDept('HR', @count);
SELECT @count;

INOUT Parameters

INOUT parameters allow passing a value in and receiving a modified value back. They behave as both input and output parameters.

Example: INOUT Parameter

DELIMITER //

CREATE PROCEDURE IncreaseSalary(INOUT current_salary DECIMAL(10,2))
BEGIN
    SET current_salary = current_salary * 1.1; -- Increase by 10%
END //

DELIMITER ;

Execution:

SET @salary = 50000;
CALL IncreaseSalary(@salary);
SELECT @salary; -- 55000

Using Parameters in MySQL Functions

MySQL functions are simpler than procedures in terms of parameter usage, only allowing IN parameters. This is because functions are meant to be used inside expressions and must be deterministic in their output given the same inputs.

Function Parameter Syntax

CREATE FUNCTION function_name (param_name datatype, ...)
RETURNS datatype
BEGIN
    -- function body
    RETURN value;
END;

Example: Function with IN Parameter

This function calculates the yearly salary based on a monthly salary input.

DELIMITER //

CREATE FUNCTION YearlySalary(monthly_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
    RETURN monthly_salary * 12;
END //

DELIMITER ;

Usage:

SELECT YearlySalary(4000); -- Returns 48000

Using Parameters Inside Procedure and Function Bodies

Assigning Parameters to Variables

You can assign parameter values to local variables for further manipulation.

DECLARE local_var datatype;
SET local_var = param_name;

Modifying OUT and INOUT Parameters

OUT and INOUT parameters can be assigned new values inside the procedure body using SET or SELECT INTO.

Example: Using Parameters with Variables

DELIMITER //

CREATE PROCEDURE CalculateBonus(
    IN emp_id INT,
    OUT bonus DECIMAL(10,2)
)
BEGIN
    DECLARE base_salary DECIMAL(10,2);

    SELECT salary INTO base_salary FROM employees WHERE employee_id = emp_id;

    SET bonus = base_salary * 0.1;
END //

DELIMITER ;

Calling Procedures and Handling Parameters

Passing Values to IN Parameters

Simply supply the argument value in the CALL statement:

CALL ProcedureName('value');

Retrieving Values from OUT and INOUT Parameters

Use user-defined variables prefixed with @ when calling the procedure and then select their values afterward:

CALL ProcedureName('input', @out_param);
SELECT @out_param;

Example: Calling a Procedure with All Parameter Types

CALL UpdateEmployeeStatus(101, 'active', @message);
SELECT @message;

Parameter Naming and Scoping Rules

• Parameter names must be unique within the procedure/function.
• Parameter names share the same namespace as local variables, so avoid naming collisions.
• Parameters are only accessible within the procedure/function body.
• Inside the body, parameter names can be referenced directly as variables.

Error Handling with Parameters

Procedures can validate input parameters and signal errors if invalid values are provided, using SIGNAL to raise exceptions:

DELIMITER //

CREATE PROCEDURE UpdateEmployeeStatus(
    IN emp_id INT,
    IN new_status VARCHAR(20)
)
BEGIN
    IF new_status NOT IN ('active', 'inactive', 'terminated') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid status value';
    END IF;

    UPDATE employees SET employment_status = new_status WHERE employee_id = emp_id;
END //

DELIMITER ;

Advanced Usage of Parameters

Using Parameters with Dynamic SQL

You can use parameters to build dynamic SQL statements inside stored procedures with PREPARE and EXECUTE statements.

DELIMITER //

CREATE PROCEDURE SearchEmployeesByColumn(
    IN column_name VARCHAR(64),
    IN search_value VARCHAR(100)
)
BEGIN
    SET @sql = CONCAT('SELECT * FROM employees WHERE ', column_name, ' = ?');
    PREPARE stmt FROM @sql;
    SET @val = search_value;
    EXECUTE stmt USING @val;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

Calling This Procedure:

CALL SearchEmployeesByColumn('department', 'Sales');

Parameters are fundamental to the power and flexibility of MySQL stored procedures and functions. IN parameters allow data to be passed in, OUT and INOUT parameters enable data to be passed back, and functions rely on IN parameters to compute and return values. Mastery of parameters facilitates modular, reusable, and secure SQL code within your database environment.

Remember to always validate inputs, use clear naming conventions, and handle parameters carefully to avoid bugs and maximize maintainability.