MySQL Stored Procedure Variables

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;

1	DECLARE variable_name datatype(size) DEFAULT default_value;

Let’s examine the statement above in more detail:

• First, you specify the variable name after the  DECLARE keyword. The variable name must follow the naming rules of MySQL table column names.
• Second, you specify the data type of the variable and its size. A variable can have any MySQL data types such as INT, VARCHAR, DATETIME, etc.
• Third, when you declare a variable, its initial value is NULL. You can assign the variable a default value by using DEFAULT keyword.

For example, we can declare a variable named  total_sale with the data type INT and default value 0 as follows:

DECLARE total_sale INT DEFAULT 0

1	DECLARE total_sale INT DEFAULT 0

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

DECLARE x, y INT DEFAULT 0

1	DECLARE x, y INT DEFAULT 0

We declared two INT variables  x and  y , and set their default values to zero.

Assigning variables

Once you declared a variable, you can start using it. To assign a variable another value, you use the SET statement, for example:

DECLARE total_count INT DEFAULT 0 SET total_count = 10;

1 2	DECLARE total_count INT DEFAULT 0 SET total_count = 10;

The value of the total_count variable is 10 after the assignment.
Besides the SET statement, you can use SELECT INTO statement to assign the result of a query to a variable. Notice that the query must return a scalar value.

DECLARE total_products INT DEFAULT 0 SELECT COUNT(*) INTO total_products FROM products

1 2 3 4

DECLARE total_products INT DEFAULT 0   SELECT COUNT(*) INTO total_products FROM products

In the example above:

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

Variables scope

A variable has its own scope, which defines its life time. If you declare a variable inside a stored procedure, it will be out of scope when the END statement of stored procedure reached.
If you declare a variable inside BEGIN END block, it will be out of scope if the END is reached. You can declare two or more variables with the same name in different scopes because a variable is only effective in its own scope. However, declaring variables with the same name in different scopes is not good programming practice.
A variable that begins with the @ sign at the beginning is session variable. It is available and accessible until the session ends.