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 needs to be declared using DECLARE before accessing it. They can be used as local variables and the input parameters inside a stored procedure. Later on we will study about the stored procedure.

DELIMITER //

CREATE PROCEDURE sp_test(var1 INT) 
BEGIN   
    DECLARE start  INT unsigned DEFAULT 1;  
    DECLARE finish INT unsigned DEFAULT 10;

    SELECT  var1, start, finish;

    SELECT * FROM places WHERE place BETWEEN start AND finish; 
END; //

DELIMITER ;

CALL sp_test(5);

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

The scope of a local variable is the BEGIN … END block within which it is declared.

Rate this post
3.5/5

Spread the words

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