2 min read
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

By DIBYAJYOTI PANDA

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- @'[email protected]' 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.

Rate this post
3.5/5

Spread the words

Share on facebook
Share on google
Share on twitter
Share on linkedin