MySQL Stored Procedure Variables

Summary: in this tutorial, you will learn about MySQL stored procedure’s variables including how to declare and use them.

A variable is a named data object whose value can change during the execution of a stored procedure.

Typically, you use variables to hold immediate results. These variables are local to the stored procedure.

Before using a variable, you need to declare it.

Declaring variables

To declare a variable inside a stored procedure, you use the DECLARE  statement as follows:

DECLARE variable_name datatype(size) [DEFAULT default_value];Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the variable after the DECLARE keyword. Ensure the variable name adheres to MySQL table column naming rules.
• Second, define the data type and length of the variable. Variables can have any MySQL data type, such as INT, VARCHAR , and DATETIME.
• Third, assign a default value to the variable using the DEFAULT option. If you declare a variable without specifying a default value, its default value is NULL.

The following example declares a variable named totalSale with the data type DEC(10,2) and default value of 0.0:

DECLARE totalSale DEC(10,2) DEFAULT 0.0;Code language: SQL (Structured Query Language) (sql)

MySQL allows you to declare two or more variables that share the same data type using a single DECLARE statement.

For example, the following example declares two integer variables totalQty and stockCount, and sets their default values to zero.

DECLARE totalQty, stockCount INT DEFAULT 0;Code language: SQL (Structured Query Language) (sql)

After declaring a variable, you can start using it.

As of MySQL 8.0.34, it is not possible to declare multiple variables with different data types using a single DECLARE statement.

For example, the following declaration will cause a syntax error:

DECLARE amount DECMIAL(10,2),
        currency CHAR(3) DEFAULT 'USD';Code language: PHP (php)

To fix the error, you need to use multiple DECLARE statements as follows:

DECLARE amount DECMIAL(10,2);
DECLARE currency CHAR(3) DEFAULT 'USD';Code language: PHP (php)

Assigning variables

To assign a variable a value, you use the SET statement:

SET variable_name = value;Code language: SQL (Structured Query Language) (sql)

For example:

DECLARE total INT DEFAULT 0;
SET total = 10;Code language: SQL (Structured Query Language) (sql)

The value of the total variable is 10  after the assignment.

In addition to the SET statement, you can use the SELECT INTO statement to assign the result of a query to a variable as shown in the following example:

DECLARE productCount INT DEFAULT 0;

SELECT COUNT(*)
INTO productCount
FROM products;Code language: SQL (Structured Query Language) (sql)

In this example:

• First, declare a variable named productCount and initialize its value to 0.
• Then, use the SELECT INTO statement to assign the productCount variable the number of products selected from the products table.

Variable scopes

A variable has its own scope, which determines its lifetime. If you declare a variable inside a stored procedure, it will be out of scope when the END statement of the stored procedure is reached.

When you declare a variable inside the BEGIN...END block, it goes out of scope once the END is reached.

MySQL allows you to declare two or more variables that share the same name in different scopes because a variable is only effective within its scope.

However, declaring variables with the same name in different scopes is not considered good programming practice.

A variable whose name begins with the @ sign is a session variable, available and accessible until the session ends.

MySQL Stored Procedure Variable Example

The following example illustrates how to declare and use a variable in a stored procedure:

DELIMITER $$

CREATE PROCEDURE GetTotalOrder()
BEGIN
	DECLARE totalOrder INT DEFAULT 0;

	SELECT
		COUNT(*)
	INTO totalOrder FROM
		orders;

	SELECT totalOrder;
END$$

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

How it works.

First, declare a variable totalOrder with a default value of zero. This variable will store the number of orders from the orders table.

DECLARE totalOrder INT DEFAULT 0;Code language: SQL (Structured Query Language) (sql)

Second, use the SELECT INTO  statement to assign the variable totalOrder the number of orders selected from the orders table:

SELECT COUNT(*)
INTO totalOrder
FROM orders;Code language: SQL (Structured Query Language) (sql)

Third, select the value of the variable totalOrder.

SELECT totalOrder;Code language: SQL (Structured Query Language) (sql)

Note that you will learn how to use variables practically in the subsequent tutorials. The example in this tutorial serves as an illustration to help you understand the concept.

This statement calls the stored procedure GetTotalOrder():

CALL GetTotalOrder();Code language: SQL (Structured Query Language) (sql)

Here is the output:

Summary

• Use variables to hold immediate results in a stored procedure.
• The scope of a variable determines the variable’s lifetime.