Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 39 ยท Procedures, Triggers and Functions

Variables and Control Flow in T-SQL

Learn T-SQL programming basics for SQL Server: DECLARE and SET, batches with GO, IF...ELSE, WHILE, BEGIN...END, temp tables and table variables, with runnable stand-ins.

Plain SQL describes what rows you want. T-SQL (Transact-SQL), SQL Server's dialect, adds a small programming language around it: variables, conditions, loops and error handling. It is what you write inside stored procedures, functions and triggers, and in the scripts you run in SQL Server Management Studio (SSMS) or Azure Data Studio.

This lesson covers the building blocks of that language. Procedural T-SQL cannot run in the browser, so the T-SQL examples are read-only and each idea has a runnable set-based stand-in. That is also good practice: in SQL Server, the set-based version is usually the faster one.

Variables

Variable names start with @. Declare them with a type, then assign with SET or SELECT:

SQL
DECLARE @CustomerId int = 42;
DECLARE @Total money, @Name nvarchar(100);

SET @Total = (SELECT SUM(Amount) FROM dbo.Orders WHERE CustomerId = @CustomerId);
SELECT @Name = FullName FROM dbo.Customers WHERE CustomerId = @CustomerId;

SELECT @Name AS Customer, @Total AS Spent;

Notes:

  • A variable lives only in its batch (a block of statements sent together). The batch separator GO is a command of SSMS and sqlcmd, not T-SQL: it ends a batch, and variables from before it are gone.
  • SELECT @x = col FROM ... assigns the value from the last row if several rows come back; SET @x = (subquery) raises an error if the subquery returns more than one row. Prefer SET when you expect exactly one row.
  • Variables are scalar. For a set of rows, use a temp table or a table variable (below).
  • System functions start with @@, for example @@ROWCOUNT (rows affected by the last statement), @@ERROR and @@TRANCOUNT. Capture @@ROWCOUNT right away, because the next statement overwrites it.

The closest portable idea is a CTE that holds your "parameters", which any query can read:

SQL Parameters as a CTE (stand-in for variables)

Press Run (or Ctrl + Enter) to see the result here.

IF...ELSE and BEGIN...END

SQL
DECLARE @Stock int = (SELECT Quantity FROM dbo.Inventory WHERE ProductId = 5);

IF @Stock IS NULL
    PRINT 'Unknown product';
ELSE IF @Stock = 0
    PRINT 'Out of stock';
ELSE
BEGIN
    PRINT 'In stock';
    UPDATE dbo.Inventory SET Quantity = Quantity - 1 WHERE ProductId = 5;
END;

IF controls only the next single statement. For several statements, wrap them in BEGIN ... END, a mistake to look out for when you add a second line under an IF. IF EXISTS (SELECT 1 FROM ...) is the idiomatic existence test.

Inside a query, use the expression form instead, CASE, or IIF(condition, a, b):

SQL CASE instead of IF

Press Run (or Ctrl + Enter) to see the result here.

WHILE, BREAK and CONTINUE

T-SQL has one loop:

SQL
DECLARE @i int = 1;
WHILE @i <= 5
BEGIN
    PRINT CONCAT('Step ', @i);
    SET @i += 1;
END;

BREAK leaves the loop, CONTINUE jumps to the next pass. Use loops for administrative work (batches, maintenance), not for row-by-row data processing: a set-based UPDATE replaces thousands of loop passes. To generate a number series without a loop, use a recursive CTE:

SQL A loop as a recursive CTE

Press Run (or Ctrl + Enter) to see the result here.

In SQL Server drop the word RECURSIVE: WITH steps(i) AS (...). See recursive CTEs.

Other flow tools

  • RETURN exits a batch or procedure, optionally with an integer status.
  • WAITFOR DELAY '00:00:05' pauses; WAITFOR TIME '23:00' waits until a time.
  • GOTO label exists but is rarely needed.
  • PRINT writes a message (the Messages tab); RAISERROR/THROW signal errors, covered in transactions and error handling.

Temp tables and table variables

You often need somewhere to keep intermediate rows.

#temp table @table variable
Created with CREATE TABLE #t (...) or SELECT ... INTO #t DECLARE @t TABLE (...)
Lives in tempdb, until the session ends (or the procedure returns) tempdb, until the batch ends
Statistics Yes No (rows are guessed; newer versions improve this)
Indexes Any Only inline PRIMARY KEY, UNIQUE, INDEX
Rolls back with transaction Yes No

Rule of thumb: a few rows, use a table variable; a lot of rows or joins that need good plans, use a #temp table. ##global temp tables are visible to all sessions and rarely a good idea.

SQL
SELECT CustomerId, SUM(Amount) AS Spent
INTO   #BigSpenders
FROM   dbo.Orders
GROUP  BY CustomerId
HAVING SUM(Amount) > 1000;

CREATE INDEX IX_Big ON #BigSpenders (CustomerId);

SQLite offers CREATE TEMP TABLE, a good stand-in:

SQL A temp table

Press Run (or Ctrl + Enter) to see the result here.

flowchart TD
  A["Batch starts"] --> B["DECLARE variables"]
  B --> C{"IF condition"}
  C -- "true" --> D["BEGIN ... END block"]
  C -- "false" --> E["ELSE branch"]
  D --> F["WHILE loop? repeat"]
  E --> F
  F --> G["Batch ends: variables disappear"]

Control flow decides which statements run; the batch decides how long variables live.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is GO in SSMS scripts?

    GO is a client command. SQL Server never sees it; the tool uses it to split the script into batches.
  2. 2. What does IF @x > 0 PRINT 'a'; PRINT 'b'; print when @x is 0?

    IF controls only the next statement. The second PRINT is outside it, so you need BEGIN...END for multi-statement bodies.
  3. 3. Which is true of table variables compared with temp tables?

    Table variables are not rolled back with the transaction and live only for the batch; they have limited statistics and indexing.
  4. 4. Why is @@ROWCOUNT usually copied into a variable right away?

    Every statement, even IF or SET, overwrites @@ROWCOUNT, so read it immediately after the statement you care about.

Summary

  • Variables start with @, are scoped to a batch and are scalar; GO separates batches in client tools.
  • IF controls one statement unless you use BEGIN...END; WHILE is the only loop, so prefer set-based code.
  • Use #temp tables for larger intermediate sets and table variables for small ones.
  • Capture @@ROWCOUNT and @@ERROR immediately.

Next: Stored procedures in T-SQL