SQL Variable

What is SQL Variable?

Like other languages, values can be stored in SQL variables. The variable holds a single data value only.

Types of SQL Variable

1. User defined variable
2. Local variable
3. Global variable ( System variable )

User defined variable

You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another. A user-defined variable in Mysql is written as @var_name where, var_name is the name of variable and can consist of alphanumeric characters ( . , _ , $ )

• A user-defined variable is session specific i.e variable defined by one client is not shared to other client and when the session ends these variables are automatically expired.
• These variables are not case-sensitive. So, @mark or @Mark both refer to same value.
• Maximum length of variables can be 64 characters. It varies from vendor to vendor.
• Variable name can include other characters like- {!, #, ^, -, ..} in its name, if they are quoted. For ex- @'var@1' or @"var^2" or @var3.
• These variables can’t be declared, they are only initialized i.e at time of declaration they should be assigned a value.
• An undeclared variable can also be accessed in a SQL statement but their values is set as NULL.
• These variables can take values from the following set of data types – integer, float, decimal, binary, nonbinary string or NULL value.

SET @var_name = value or expression;

or 

SET @var_name := value or expression;

SET @a = 5;

SET @a = 7;

SELECT @a + @b;
+---------+
| @a + @b |
+---------+
|      12 |
+---------+

Local Variables

Local variables are normally used inside a procedure just like other programming languages. It needs to be declared using DECLARE keyword before accessing it. They can also be used as the input parameters to a stored procedure. However later on we will study more about the stored procedure.

DELIMITER //

CREATE PROCEDURE test(var1 INT) 
BEGIN   
    DECLARE var2  INT unsigned DEFAULT 1;  
    DECLARE var3  INT unsigned DEFAULT 10;

    SELECT  var1, start, finish;

END; //

DELIMITER ;

CALL test(5);

If the DEFAULT clause is missing, the initial value is NULL. The difference between a procedure variable and an user-defined variable is that procedure variable is re-initialized to NULL each time the procedure is called, while the session-specific variable doesn’t.

The scope of a local variable is within BEGIN & END block always.