MySQL Stored Procedure Variables

MySQL Stored Procedure Variables

In MySQL, stored procedures support two types of variables for use within the procedure: Local Variables and User-Defined Variables. These variables enable you to store intermediate results, pass values between queries, and control procedure logic.

Types of Variables in MySQL Stored Procedures

1. Local Variables:

   • Declared within a stored procedure using the DECLARE statement.
   • The scope is limited to the stored procedure in which they are declared.
   • Automatically destroyed when the procedure ends.
   • Cannot use @ in their names.

2. User-Defined Variables:

   • Prefixed with @ and accessible globally within the session.
   • Persist for the duration of the session unless explicitly reset.
   • Do not require a declaration.

1. Declaring and Using Local Variables

Syntax for Local Variables

DECLARE variable_name datatype [DEFAULT value];

• variable_name: The name of the variable.
• datatype: The data type of the variable.
• DEFAULT value: Optional; specifies the initial value of the variable. If omitted, the value is NULL.

Example: Local Variable Declaration

DELIMITER $$

CREATE PROCEDURE calculate_total(IN order_id INT, OUT total_amount DECIMAL(10,2))
BEGIN
    DECLARE subtotal DECIMAL(10,2);
    DECLARE tax_rate DECIMAL(5,2) DEFAULT 0.08;
    
    -- Calculate subtotal
    SELECT SUM(price * quantity) INTO subtotal
    FROM order_items
    WHERE order_id = order_id;

    -- Calculate total amount
    SET total_amount = subtotal + (subtotal * tax_rate);
END$$

DELIMITER ;

Explanation:

1. Declares two local variables: subtotal and tax_rate.
2. The subtotal is calculated using a query.
3. The tax_rate variable is used in the computation of total_amount.

Assigning Values to Local Variables

1. Using SET:

   SET variable_name = value;
   

2. Using SELECT ... INTO:

   SELECT column_name INTO variable_name FROM table_name WHERE condition;
   

2. Using User-Defined Variables

Syntax for User-Defined Variables

SET @variable_name = value;

Example: User-Defined Variables

SET @total_sales = 0;

SELECT SUM(sales) INTO @total_sales
FROM sales_data
WHERE year = 2025;

SELECT @total_sales AS TotalSales;

Key Differences from Local Variables:

• Can be accessed outside the stored procedure within the same session.
• Useful for debugging or sharing values between multiple queries.

Combining Local and User-Defined Variables

You can use both local and user-defined variables in a stored procedure. However, prioritize local variables for encapsulation and user-defined variables for debugging or session-wide operations.

Practical Use Cases for Stored Procedure Variables

1. Intermediate Calculations:

   • Store temporary results for reuse within a procedure.

2. Dynamic Query Construction:

   • Use variables to construct SQL statements dynamically.

3. Control Flow:

   • Manage procedural logic (e.g., loops, conditionals).

Example: Complete Stored Procedure with Variables

DELIMITER $$

CREATE PROCEDURE process_order(IN order_id INT, OUT final_amount DECIMAL(10,2))
BEGIN
    DECLARE subtotal DECIMAL(10,2);
    DECLARE discount DECIMAL(10,2) DEFAULT 0.10;
    DECLARE tax DECIMAL(10,2) DEFAULT 0.08;
    DECLARE total DECIMAL(10,2);

    -- Calculate the subtotal
    SELECT SUM(price * quantity) INTO subtotal
    FROM order_items
    WHERE order_id = order_id;

    -- Apply discount
    SET subtotal = subtotal - (subtotal * discount);

    -- Calculate tax
    SET total = subtotal + (subtotal * tax);

    -- Set the final amount
    SET final_amount = total;
END$$

DELIMITER ;

Key Points to Remember

1. Scope:

   • Local variables are limited to the stored procedure's scope.
   • User-defined variables persist for the session but can be accessed outside the procedure.

2. Null Values:

   • Variables default to NULL unless explicitly initialized.

3. Variable Naming:

   • Avoid using column names or reserved keywords as variable names.
   • Use meaningful names for better readability.

4. Performance:

   • Use local variables for intermediate calculations to avoid cluttering the session space.

Debugging with Variables

• Use SELECT variable_name or SELECT @variable_name to inspect variable values during development and debugging.

Let me know if you need more examples or have specific questions about stored procedure variables!