What is a variable?
A variable is a named memory location where you can store data or values temporarily during stored procedure execution.
It is called named because user can provide a meaningful name to a variable. This name is then used to store and fetch(or read) data from variable.
Benefit of variable
A variable is a temporary data location, thus, in a scenario where you want to store data of one operation and require this data in another operation, variables are very beneficial.
Consider scenarios below where a variable becomes useful
- Swapping the values of two or more columns of a table.
- Suppose you have a couple of SQL queries in a procedure where a value from 1 query needs to be used in WHERE clause in second query, you can store it in the variable and read it back.
This can also be done using an inner query but using variable in a stored procedure is a better alternative. Further, it will also be useful if some operation needs to be performed on that value before using it.
Syntax
A variable needs to be declared before using it which is done using below syntax.
DECLARE [variable name] [data type] DEFAULT [initial value];
where,
DECLARE keyword is used for declaring a variable. It is followed by a user defined name by which a variable will be identified.
Variable name is followed by the data type of the variable. It defines the type of values that the variable would hold. Example, INT, DECIMAL etc.
If you want an initial value for the variable, then define it using DEFAULT keyword followed by the initial value. This is optional.
If you do not specify the initial value using DEFAULT, then it will be null.
Examples of variable declaration are as below
DECLARE txnid INT;
DECLARE guest_name VARCHAR(100) DEFAULT null;
DECLARE cost DOUBLE DEFAULT 0.0;
Remember that all the variables required by the procedure should be declared just after the BEGIN statement. You can not declare variables anywhere or just when required.
Assigning variable values
Once a variable is declared, it can be assigned values. This value may be assigned directly or as a result of some SQL query.
Direct assignment of a variable value is done using SET statement which is like an initialization statement. Example,
SET txnid = 18394;
Remember that the value should be of the same type as defined while declaring the variable.
A variable can also be assigned value as a result of an SQL query using INTO keyword followed by the name of variable. Example,
DECLARE lasttxnid INT;
SELECT MAX(id) INTO lasttxnid FROM transaction;
This is similar to how OUT and INOUT parameters were assigned values.
Variable example
Below is a complete example of using a variable in MySQL stored procedure. As before, we will be using our sample STUDENT table for illustration.
DELIMITER %%
CREATE PROCEDURE variable_usage()
BEGIN
— declare variable
DECLARE student_country varchar(30);
— fetch John’s country and store it in variable
SELECT country INTO student_country FROM student WHERE name=’John Doe’;
— fetch all students whose country is different than John’s country
SELECT name FROM STUDENT WHERE country != student_country;
END %%
DELIMITER ;
Above procedure fetches the country for student whose name is John Doe and stores it in a variable. Then it fetches the names of all those students whose country is different than that of John Doe’s.
Though this can also be done using an inner query but the usage of variable makes it simpler and more flexible. This procedure is invoked as
CALL variable_usage;
Output is
Notice the use of INTO keyword to store value of a column into the variable.
