Variables in SQL Server (Transact-SQL)

This article covers the following topics: 

• Definition of Variable. 
• Applications of Variables in SQL Server. 
• Varieties of variables and an in-depth analysis of each type. 
• Steps for declaring a variable. 

What is Variable? 

A Variable can be defined as an entity that can contain a singular data value of a designated datatype, such as INT, DATE, and VARCHAR, etc. 

The following are the uses of the variables: 

Variables in SQL Server have various applications, including but not limited to: 

• Storing and manipulating data temporarily during query execution. 
• Using variables to pass data values between stored procedures or functions. 
• Employing variables in dynamic SQL statements to construct dynamic queries. 
• Facilitating the creation of conditional expressions and loops. 
• Simplifying complex queries by breaking them into smaller, more manageable parts. 

Types of Variables in SQL Server: 

There are two types of Variable in SQL Server 

1. Local Variable 

2. Global Variable. 

1. Local Variable (User Defined Variables): 

Local Variables also known as User-Defined Variables, declared by the User.  
In simple words, a variable which is defined by the user is called Local Variable or User Defined Variable. 

• A Local Variable is denoted with the “@” symbol by default. 
• Local Variables can be used with the current Procedure or Batch within any given session. 

2. Global Variable (System Variables): 

Global variables are pre-defined by the system. It means system maintains the Global Variables. 

• It can’t be declared by a user. 
• Global Variables Starts with @@. 
• Global Variables stores the session related information. 

Declaring a Variable: 

Syntax:

DECLARE @Variable_Name AS DataType

The “AS” keyword used in the previous syntax example is not required and is optional. The following example illustrates that the query can be written without using the “AS” keyword. 

Note: By default, DECLARE initializes variable to NULL. 

Example – Declare a Variable named ABC with VARCHAR DataType

DECLARE @ABC VARCHAR(20)
or
DECLARE @ABC AS VARCHAR(20)

We can declare multiple variables at once, as shown in the following syntax: 

DECLARE @Variable1 DataType, @Variable2 DataType, @Variable3 DataType……….

Assigning a value to a Variable – 

There are three different ways of assigning a value to a Variable, so we will discuss one by one all methods to assign a values:  

1. During the declaration of Variable – 

At the time of declaration, it is possible to assign a value to a variable. 

We will now declare a variable and assign a value to it. To display the value of the variable, we will use the “PRINT” statement. 

DECLARE @ABC VARCHAR(20) = ‘HELLO’
PRINT @ABC

2. Using SET – 

• SET is an ANSI Standard for Variable assignment. 
• SET can assign only one variable at a time. 
• If we are assigning a value from a query, SET can only assign a SCALAR value. If query returns multiple values/rows then SET will raise an error. 
• When we are assigning a value from a query if there is no value returned then SET will assign NULL. 

Let us examine an example to comprehend how to assign a value using the “SET” keyword. 

DECLARE @ABC VARCHAR(20)
SET @ABC = ‘HELLO’
PRINT @ABC

3. Using SELECT – 

• SELECT is not an ANSI Standard for Variable assignment. 
• SELECT has an ability that it can assign multiple variable at a time. 
• When we are assigning a value from a query if there is no value returned then SELECT will not make the assignments at all. 

Let us examine an example to comprehend how to assign a value using the “SELECT” statement.

DECLARE @ABC VARCHAR(20), @xyz INT
SELECT @ABC = ‘Hello’, @xyz = 1
PRINT @ABC;
PRINT @xyz;

If the “SET” keyword is used in the above syntax for assigning values, it will result in an error because it can only assign one variable at a time. In contrast, the “SELECT” statement can perform multiple assignments simultaneously. 

The following are some commonly used Global Variables: 

Here are some key points about Global Variables: 

• Global variables are pre-defined system variables. 
• They provide information about the current user environment in SQL Server. 
• SQL Server has multiple Global Variables available that are useful for Transact-SQL. 

Let’s discuss the Global variable which are commonly used:  

• @@VERSION – This Global Variable is utilized to determine the present version of the SQL Server software. (Note: an alias can also be assigned to specify the name of column of the output) 

SELECT @@VERSION;

• @@LANGUAGE – this variable is used to find the name of language that is currently used by SQL Server. 

@@SERVERNAME à this is used to find the name of the machine/computer on which SQL Server is running. 

@@ERROR – this is used to get the error number for last SQL statement Executed. If this value is zero than there were no error otherwise it returns the error.  

SELECT * FROM [HR].[Employees]
IF (@@ERROR <> 0)
PRINT ‘Error found’
ELSE
PRINT ‘Error not found’;

@@ROWCOUNT à This is used to fetch the number of rows affected by the last SQL statement.  

SELECT * FROM [HR].[Employees]
SELECT @@ROWCOUNT as ‘Number of rows affected’

Level1:

DECLARE @INT1 INT, @INT2 INT, @INT3 INT
SET @INT1 = 2
SET @INT2 = 4
SET @INT3 = @INT1 * @INT2
PRINT @INT3

Level2:

DECLARE @VIVA VARCHAR(25), @VIVA2 VARCHAR(25), @VIVA3 VARCHAR(25)——–COMBINING 2 STRINGS
SET @VIVA = ‘HELLO’
SET @VIVA2 = ‘ WORLD ‘
SET @VIVA3 = @VIVA + @VIVA2
PRINT @VIVA3

Difference between SET and SELECT: 

In SQL Server, there are two methods of assigning a value to a variable: using the “SET” keyword or the “SELECT” statement. Here are the key differences between the two: 

• The “SET” keyword is only capable of assigning a single value to a single variable at a time, while the “SELECT” statement can assign multiple values to multiple variables at once. 
• The “SELECT” statement can assign values from various data sources, including tables, views, and functions, whereas the “SET” keyword can only assign constant values, variables, or expressions. 
• The “SELECT” statement allows the use of aliases to assign names to the variables being assigned values, whereas the “SET” keyword does not. 
• The “SET” keyword is generally considered to be faster than the “SELECT” statement when assigning values to a single variable due to its simplicity and straightforwardness. However, the performance difference between the two is generally negligible when assigning values to multiple variables.

Conclusion: 

Variables are an important concept in SQL Server that allow you to store and manipulate data within your queries. They are used to hold single values of a specified data type and can be either local or global in scope. SQL Server provides various types of variables, including numeric, string, date/time, and bit. You can declare and assign values to variables using the “DECLARE”, “SET”, or “SELECT” statements. Additionally, global variables are pre-defined system variables that provide information about the current user environment for SQL Server. Overall, variables provide a flexible and powerful way to write complex queries and procedures in SQL Server.