Variable is a data object. Its value can be changed during the execution of the stored procedure. The results of the procedure execution are stored in variables. To declare variable inside procedure we use syntax given below:

DECLARE variableName datatype(size) DEFAULT defaultValue;

Where,

DECLARE is a keyword that declares the variable name.

The variableName is name of the variable.

The datatype of the variable. It can be INT, VARCHAR, DATETIME ..etc.

DEFAULT is the keyword which gives default value for the variable. If it is missing variable is assigned 'null'.

In the example below variables are declared and default values are assigned:

DECLARE a,b INT DEFAULT 5

DECLARE sum INT DEFAULT 0

DECLARE myName VARCHAR(10) DEFAULT 'Sundeep';

Assigning variables

Variables can be assigned values after declarations using 'SET' and 'SELECT INTO' statements as below:

DECLARE maxCount INT DEFAULT 0

SET maxCount = 100;

This sets the value of 'maxCount' as 100.

DECLARE itemName INT DEAFULT 0

SELECT item(*) INTO itemName FROM tblProducts;

This copies the number of items in item column from tblProducts to itemName variable.

Scope of the variables defined within stored procedure ends with the END statement. It cannot be used outside the block.