How to Declare and Assign a Value to a Variable in MySQL Stored Procedure

In this tutorial, we are going to see How to declare and assign a value to a variable in MySQL stored procedure. Local variables are variables declared in a stored procedure. They are valid only in BEGIN … END block and can have any SQL data type.

A variable is a named object whose value can change during the execution of the procedure. You usually use variables in stored procedures to maintain immediate results. These variables have local scope within the stored procedure.
 

 
Before using a variable, you must declare it. To understand this, create the following procedure:

DELIMITER $
Create Procedure ShowVariables()
   BEGIN
   DECLARE A INT DEFAULT 10;  #Declare an integer variable with default value
   DECLARE B INT;
   DECLARE C INT;
   DECLARE D INT;
   SET B = 20;                #Assign the value 20 to the variable B
   SET C = 15;
  SET D = A + B + C;
  SELECT A,B,C,D;
  END$
Delimiter ;

Once you have registered the stored procedure, you can invoke it using the CALL statement:

CALL ShowVariables();

Output:

+---------+-----------+----------+-------------+
|     A   |     B     |     C    |      D      |
+---------+-----------+----------+-------------+
|    10   |     20    |     15   |     45      |
+---------+-----------+----------+-------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More