Summary: in this tutorial, you will learn about variables in the stored procedure, how to declare, and use variables. In addition, you will learn about the scopes of variables.

A variable is a named data object whose value can change during the stored procedure execution. You typically use variables in stored procedures to hold immediate results. These variables are local to the stored procedure.

Before using a variable, you must declare it.

Declaring variables

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

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

In this syntax:

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

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

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. The following example declares two integer variables  x and  y, and set their default values to zero.

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

Assigning variables

Once a variable is declared, it is ready to use. To assign a variable a value, you use the SET statement:

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

For example:

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:

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 that defines its lifetime. If you declare a variable inside a stored procedure, it will be out of scope when the END statement of stored procedure reaches.

When you declare a variable inside the block BEGIN END, it will be out of scope if 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 in its scope. However, declaring variables with the same name in different scopes is not good programming practice.

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

Putting it all together

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

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

How it works.

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

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:

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

Third, select the value of the variable 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 is just an illustration so that you understand the concept.

This statement calls the stored procedure GetTotalOrder():

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

Here is the output:

In this tutorial, you have learned how you how to declare and use variables inside stored procedures.