Variables in Stored Procedures
Declaring variables
Variables are used in stored procedure to store the immediate result. You can declare a variable by the following syntax:
DECLARE variable_name datatype(size) DEFAULT default_value;
Followed DECLARE statement is the variable name. The variable name should follow the naming convention and should not be the same name of table or column in a database. Next you can specify the data type of the variable, it can be any primitive type which MySQL supports such as INT, VARCHAR and DATETIME…along with the data type is the size of the variable. When you declare a variable, its initial value is NULL. You can also assign the default value for the variable by using DEFAULT statement. For example, we can define a variable name total_sale with the data type INT and default value is 0 as follows:
DECLARE total_sale INT DEFAULT 0
To declare two or more variables with the same data type we can use only just one DECLARE such as:
DECLARE x, y INT DEFAULT 0
We declared two variables x and y with the same data type INT and their default value is zero.
Assigning variables
Once you declared a variable, you can start using it. To assign other value to a variable you can use SET statement, for example:
DECLARE total_count INT DEFAULT 0
SET total_count = 10;
The total_count variable’s value now is ten (10).
Beside SET statement, we can use SELECT … INTO to assign a query result to a variable.
DECLARE total_products INT DEFAULT 0
SELECT COUNT(*) INTO total_products
FROM products
In the example above, we declare a variable total_products and initialize its value to zero. Then we use SELECT … INTO statement to assign the variable total_products with the total products in products database table.
Variables scope
A variable has its own scope. If you declare a variable inside a stored procedure, it will be out of scope when the END of stored procedure reached. If you defined a variable inside block BEGIN/END inside a stored procedure it will be out of scope if the END reached. You can declare two variables or more variables with the same name in different scopes; the variable only is effective in its scope.
A variable with the ‘@’ at the beginning is session variable. It exists until the session end.