In T-SQL, a local variable is a named temporary memory object with a specific data type, designed to store a value temporarily during the execution of a script or batch of statements.
Up to this point, we have written queries that are static. You execute a statement and get a result. But the real world requires logic: “If this happens, do that”, “save this value to use later”, “repeat this 10 times”.
To achieve this, T-SQL provides us with procedural programming tools. The first and most fundamental is the use of temporary memory, that is, Variables.
A variable is nothing more than a container with a name, where we temporarily store data to use it later within our script.
Imagine you want to find all the orders for a specific customer. You could write the customer ID 10 in five different places in your query. But if you later want to search for customer 20, you would have to change it in five places.
With a variable, you store the ID in one place (@CustomerID) and use that name in the rest of the code.
Variable Declaration (DECLARE)
In T-SQL, all local variables must follow two rules:
- Its name must start with an at sign (@).
- They must be declared before use with the keyword DECLARE.
-- Syntax: DECLARE @Name DataType;
DECLARE @Age INT;
DECLARE @CustomerName VARCHAR(100);
DECLARE @StartDate DATETIME;
When you declare a variable, its initial default value is always NULL.
From recent versions of SQL Server, you can declare and initialize (assign a value) on the same line:
DECLARE @Tax DECIMAL(10, 2) = 0.21;
Assigning Values: SET vs SELECT
There are two ways to put data into a variable once declared. Although they seem to do the same thing, they have important differences.
SET (The Standard)
It is the method recommended by the ANSI standard. It only allows assigning one variable at a time.
DECLARE @Price DECIMAL(10,2);
SET @Price = 100.50;
SELECT (The T-SQL way)
It is specific to SQL Server. It allows assigning multiple variables in a single statement and is very useful for saving query results.
DECLARE @FirstName VARCHAR(50);
DECLARE @LastName VARCHAR(50);
-- Assign both at once by pulling data from a table
SELECT
@FirstName = Nombre,
@LastName = Apellido
FROM Empleados
WHERE EmpleadoID = 1;
Use SET for simple values and SELECT only when you need to pull data from a table.
Using Variables in Queries
Once the variable has a value, you can use it in WHERE, INSERT, UPDATE or even to print messages.
Example: Dynamic Filtering Script
DECLARE @SearchCity VARCHAR(50);
DECLARE @DateLimit DATE;
-- We set the parameters here
SET @SearchCity = 'Madrid';
SET @DateLimit = '2023-01-01';
-- We use the variables in the query
SELECT * FROM Pedidos P
INNER JOIN Clientes C ON P.ClienteID = C.ClienteID
WHERE C.Ciudad = @SearchCity
AND P.FechaPedido >= @DateLimit;
Variable Scope
A local variable only “lives” within the Batch in which it is executed. In SQL Server Management Studio (SSMS), a batch is usually delimited by the separator command GO.
DECLARE @MyNumber INT = 10;
SELECT @MyNumber;
GO -- The batch ends here. The variable @MyNumber is destroyed.
SELECT @MyNumber; -- ⛔ ERROR! The variable is not declared.
If you try to execute the second part, SQL Server will tell you:
Must declare the scalar variable “@MyNumber”.
This means you cannot declare a variable in one tab and use it in another, not even in the same tab if there is a GO in between.
