T-SQL Variables, Batches, and Control-of-Flow: Beyond Basic Queries

This is where SQL Server stops being just a query language and starts behaving like a real programming environment. Everything in the SQL Server Fundamentals course was declarative — describe the result you want, let the engine figure out how. Variables, IF/ELSE, and loops introduce procedural logic on top of that, which is exactly what stored procedures, functions, and triggers (the rest of this course) are built from.

Declaring and Using Variables

DECLARE @threshold DECIMAL(10,2) = 50.00;
DECLARE @productCount INT;

SELECT @productCount = COUNT(*) FROM dbo.Product WHERE price > @threshold;
PRINT 'Products above threshold: ' + CAST(@productCount AS VARCHAR(10));

IF @productCount > 0
    PRINT 'At least one premium product exists.';
ELSE
    PRINT 'No premium products found.';

Notice SELECT @productCount = COUNT(*) ... — this is SQL Server’s idiom for assigning a query’s result directly into a variable, distinct from a normal SELECT that returns a result set. If the query returns zero rows, the variable is set to NULL, not left unchanged; if it returns multiple rows, the variable silently ends up holding the last row’s value, which is rarely what you want and worth watching for.

The Batch-Scoping Trap

A batch is a group of statements sent to SQL Server together, separated by GO (a client-side signal understood by SSMS/sqlcmd, not real T-SQL — the server itself never sees it). Variables declared in one batch don’t exist in the next, because each batch is compiled and executed as an entirely separate unit. This trips people up constantly when copy-pasting scripts with stray GO statements in the middle, or when a script generator inserts a GO you didn’t expect.

-- Also worth knowing: DDL statements sometimes REQUIRE their own batch
CREATE VIEW dbo.vw_Test AS SELECT 1 AS col; -- must be the first/only statement in its batch
GO

WHILE Loops: A Last Resort, Not a First Instinct

DECLARE @i INT = 1;
WHILE @i <= 3
BEGIN
    PRINT 'Iteration ' + CAST(@i AS VARCHAR(10));
    SET @i += 1;
END;

WHILE loops process one row at a time and are almost always slower than an equivalent set-based UPDATE/SELECT — this is the single biggest mental adjustment for developers coming from procedural languages, where a for-loop is the default tool. In SQL Server, reach for a loop only when the logic genuinely can't be expressed as a single set operation (batch-processing a huge table in chunks to avoid one giant transaction is a legitimate use case; iterating row by row to do what an UPDATE could do in one statement is not).

-- The set-based equivalent of "loop through rows and apply a 10% discount" —
-- no loop needed, and dramatically faster on any real table size:
UPDATE dbo.Product SET price = price * 0.9 WHERE category = 'Clearance';

BEGIN/END Blocks and Nesting

DECLARE @orderTotal DECIMAL(10,2) = 150.00;

IF @orderTotal > 100
BEGIN
    IF @orderTotal > 500
        PRINT 'Free shipping + gift wrap';
    ELSE
        PRINT 'Free shipping';
END
ELSE
    PRINT 'Standard shipping rate applies';

Unlike braces in C-style languages, BEGIN/END is only strictly required to group multiple statements under one IF/WHILE — a single statement doesn't need it, as the ELSE branch above shows. Many teams require BEGIN/END everywhere regardless, purely for consistency and to avoid a bug where someone adds a second statement to a branch and forgets it silently falls outside the IF.