T-SQL Variables

Master T-SQL variables — DECLARE syntax for single and multiple variables, the three assignment methods (inline / SET / SELECT), the critical SET vs SELECT differences for multi-row and zero-row queries, compound assignment operators, and batch-scope rules with GO.

A variable in T-SQL is a named container that holds a single value of a specific data type. You declare it, give it a value, and use it later in the same batch or stored procedure — just like in any programming language. Variables let you compute intermediate results, pass values between statements, parameterize queries, and write logic that's more flexible than hard-coded SQL.

Why You'll Use Variables Constantly

• Capture a value from one query and use it in the next.
• Pass parameters into stored procedures and user-defined functions.
• Build dynamic conditions for IF / WHILE control flow.
• Track running totals, counters, or status flags inside loops.
• Construct dynamic SQL strings safely.

T-SQL has two kinds of variables. Local variables are the ones you create; global variables are system values SQL Server maintains for you.

You introduce a local variable with DECLARE. Every local variable starts with @.

Syntax

• @local_variable — the variable name (must start with @).
• data_type — any T-SQL type: INT, VARCHAR(50), DATETIME2, etc.
• = initial_value — optional. If omitted, the variable starts as NULL.

Single Variable

DECLARE @EmployeeID INT;          -- defaults to NULL

Multiple Variables — Two Approaches

Option A: One DECLARE per variable (verbose but very clear):

DECLARE @EmployeeID    INT;
DECLARE @EmployeeName  VARCHAR(50);
DECLARE @HireDate      DATETIME;

Option B: A single DECLARE with comma-separated entries:

DECLARE @EmployeeID    INT,
        @EmployeeName  VARCHAR(50),
        @HireDate      DATETIME;

Once a variable exists, you can give it a value in any of three ways:

1. Inline at declaration time, with = in the DECLARE.
2. Later, with the SET statement.
3. Later, with the SELECT statement.

The first two are simple. The third has subtle behaviors that catch out experienced developers — covered below in SET vs SELECT.

DECLARE @EmployeeID INT = 100;
PRINT @EmployeeID;

The SET statement assigns a value to exactly one variable per statement. It's the ANSI-standard way and the most predictable.

Syntax

DECLARE @EmployeeID INT;
SET @EmployeeID = 100;
PRINT @EmployeeID;

Each variable needs its own SET statement:

DECLARE @EmployeeID    INT,
        @EmployeeName  VARCHAR(50);

SET @EmployeeID   = 100;
SET @EmployeeName = 'Alex';

PRINT @EmployeeID;
PRINT @EmployeeName;

SELECT can also assign to variables, and unlike SET it can assign to several variables at once in a single statement:

Syntax

DECLARE @EmployeeID    INT,
        @EmployeeName  VARCHAR(50);

SELECT @EmployeeID   = 100,
       @EmployeeName = 'Alex';

PRINT @EmployeeID;
PRINT @EmployeeName;

SET and SELECT look interchangeable for simple constants, but they behave differently in two crucial situations: when the source query returns multiple rows, and when it returns no rows at all. Getting this wrong is one of the most common silent bugs in T-SQL code.

CREATE TABLE Employees (EmployeeID INT, Name VARCHAR(50));
INSERT INTO Employees VALUES (1, 'Alice'), (2, 'Bob');

DECLARE @name_set    VARCHAR(50) = 'unchanged';
DECLARE @name_select VARCHAR(50) = 'unchanged';

-- Both queries match no rows (ID 999 doesn't exist)
SET    @name_set    = (SELECT Name FROM Employees WHERE EmployeeID = 999);
SELECT @name_select =  Name FROM Employees WHERE EmployeeID = 999;

PRINT 'SET    result: ' + ISNULL(@name_set,    '');
PRINT 'SELECT result: ' + ISNULL(@name_select, '');

The two assignments did different things. SET gave a clean NULL when no row matched. SELECT silently left the variable holding its old value — which can be a serious bug if your code expects "no row" to mean "no value".

DECLARE @name_select VARCHAR(50);

-- Two rows match. SELECT silently picks one (order is not guaranteed).
SELECT @name_select = Name FROM Employees;
PRINT 'SELECT got: ' + @name_select;

-- The same query with SET fails:
DECLARE @name_set VARCHAR(50);
SET @name_set = (SELECT Name FROM Employees);   -- raises an error

SELECT happily kept the value from some row (which one is up to the optimizer). SET raised Msg 512 — an explicit error that's hard to miss.

The most common real-world pattern: assign one or more variables from a single row of a SELECT.

DECLARE @id    INT,
        @name  VARCHAR(50);

SELECT @id   = EmployeeID,
       @name = Name
FROM   Employees
WHERE  EmployeeID = 1;

PRINT 'ID:   ' + CAST(@id AS VARCHAR(10));
PRINT 'Name: ' + @name;

For arithmetic and bitwise updates, T-SQL supports compound operators that combine an operation with assignment — exactly like C, Java, or C#.

DECLARE @counter INT = 10;

SET @counter += 5;        -- 15
PRINT @counter;

SET @counter *= 2;        -- 30
PRINT @counter;

SET @counter -= 7;        -- 23
PRINT @counter;

A T-SQL batch is the unit of code SQL Server compiles together. Variables exist only within their batch. The keyword GO ends one batch and starts the next — and it destroys all variables from the previous batch.

DECLARE @x INT = 100;
PRINT @x;          -- works: 100
GO

PRINT @x;          -- compile error — @x no longer exists

• Forgotten length on VARCHAR. DECLARE @x VARCHAR; defaults to length 1. Always specify: VARCHAR(50).
• Concatenation type mismatch. 'count is ' + @int_var raises an error if the string isn't a number. Wrap with CAST(@int_var AS VARCHAR(10)).
• Implicit truncation. Assigning 'longer string than the variable' to a VARCHAR(10) truncates without warning. The destination type's length always wins.
• Variables vs columns with the same name. Inside a query, WHERE Name = Name matches every row; you almost certainly meant WHERE Name = @Name. Adding the @ isn't optional — those are different things.

• Local variables start with @ and live within the current batch or procedure; @@ identifiers are system functions you can read but not declare.
• Use DECLARE to create variables; either one declaration per variable, or one DECLARE with comma-separated entries — never repeat DECLARE without its keyword.
• Assign with inline initialization, SET, or SELECT.
• Prefer SET by default — it errors loudly when a query returns multiple rows. Use SELECT for multi-variable assignment from a single row.
• Compound operators (+=, -=, *=, etc.) make running totals and counters tidy.
• GO ends a batch — your variables don't survive it. Plan accordingly.