MySQL – Stored Procedure Variables

Variables can be used in the stored procedure to hold input parameters, values inside the block, and immediate results. Variables in a stored procedure have a local scope. A variable must be defined before it can be used within a stored procedure.

Let us now examine how we can declare a variable within a stored procedure.

How to Declare Variables in Stored Procedures

To specify a variable in MySQL, we use the DECLARE keyword. The syntax for declaring a variable is as follows:

DECLARE variable_name datatype(size) [DEFAULT default_value];

Where,

• DECLARE – It declares the variable.
• variable_name – Any name that meets the conditions can be used as a variable.
• datatype – It defines the data type of a variable that will be used throughout the block of code. The size of a variable depends on the data type.
• DEFAULT default_value – You can specify a default value for the variable using the DEFAULT option. The DEFAULT option is optional, and you can use the SET statement to set the value to a variable without using the DEFAULT option.

Example

DELIMITER //

CREATE PROCEDURE proc_demo()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE output VARCHAR(100) DEFAULT '';
WHILE i < 10 DO
SET output = CONCAT(output, i , ',');
SET i = i + 1;
END WHILE;
SELECT output;
END //

The above example declares two variables i and output and assigns them default values of 0 and ”, respectively.

Using a while loop, we increment the value of i from 0 to 9. In order to create a string of 0 to 9 digits, we used the CONCAT function to concatenate the previous output and the new digit.

It is set to i+1 every time until i reaches 10 and the loop ends.

To print the results, we use the SELECT statement.

As a result of calling the above procedure, we receive the following output:

call proc_demo();

We have achieved the expected result of a string containing digits from 0 to 9.