Variables in Transact SQL

Variables are objects that store values in order to retrieve them and use them in other statements. They are often used in control flow structures, stored procedures, cursors, scripts, and batches.

We can mainly differentiate two types of variables:

1- Local variables: These are the variables declared by the user and they must be preceded by one at sign (@). As their name suggests, these variables are local in scope, being visible only within the batch or procedure where they are declared. They will also be specific to each connection, automatically releasing when leaving it.

2- Global functions (Global variables): They are a special type of variable whose values are kept by the server. It is important to note that there are no global variables in Transact SQL. There are, however, certain system functions whose syntax is preceded by two at signs (@@), that were called global variables in earlier versions of SQL Server.

DECLARE

The DECLARE syntax is very simple:

DECLARE @variableName data_type;

Here are some examples:

DECLARE @a INT;
DECLARE @b NVARCHAR (50)
DECLARE @c DECIMAL (5,2) -- Allows 2 decimal places
DECLARE @customerNumber INT;

It is also possible to declare multiple variables in a single statement:

DECLARE @d DATE, @e VARCHAR (10);

The variables declared and not set, will store a NULL value by default;

SET

Setting variables refers to the introduction of values that can be stored according to the data type in which they are declared. The syntax is:

SET @variableName = VALUES

Here are some examples:

SET @a = 21;
SET @b = 'Carlos';
SET @c = 29.95;

They can also store the value returned by a SELECT:

SET @customerNumber = (SELECT COUNT (1)
                      FROM Customers);

SELECT @customerNumber;

We also have the option of declaring and setting a variable in a single statement:

DECLARE @name VARCHAR(50) = 'Jessica';