User-Defined vs. Stored Procedure Variables

MySQL Tutorials - Herong's Tutorial Examples

∟System, User-Defined and Stored Procedure Variables

∟User-Defined vs. Stored Procedure Variables

This section provides a tutorial example to show you differences between user-defined variables and stored procedure variables: syntax, declaration and scope.

What Are the Differences between User-Defined Variables and Stored Procedure Variables? User-Defined Variables and Stored Procedure Variables are having 3 main differences:

1. Different Syntax: - Stored Procedure Variables can not start with "@". User-Defined Variables must be prefixed with "@".

2. Type Declaration: - Stored Procedure Variables must be declared with specific data types. User-Defined Variables do not need any type declaration.

3. Different Scope: - Stored Procedure Variables are only valid locally inside the stored procedure where are introduced. User-Defined Variables are valid globally in the entire client session.

Here is an example client session showing you the differences between user-defined variables and stored procedure variables.

mysql> SET @PI := 3.14;

mysql> DELIMITER '/';
mysql> CREATE PROCEDURE GetArea(IN Radius REAL, OUT Area REAL)
    -> BEGIN
    ->  DECLARE Square REAL;
    ->  SET Square := Radius*Radius;
    ->  SELECT @PI*Square INTO Area;
    -> END/

mysql> DELIMITER ';'/

mysql> CALL GetArea(2.0, @area);

mysql> SELECT @area;
+-------+
| @area |
+-------+
| 12.56 |
+-------+

mysql> DROP PROCEDURE IF EXISTS GetArea;