Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 39 ยท Getting Started

T-SQL Basics โ€“ Batches, Variables and Control Flow

Learn how T-SQL batches work, declare and use variables, print output, branch with IF and loop with WHILE, plus the system functions you will use daily.

Standard SQL describes data; T-SQL adds a small programming language around it. You can declare variables, make decisions, loop, and return messages, all inside a script. Learning these building blocks now makes every later lesson, from stored procedures to error handling, easier to read.

The examples in this lesson are real T-SQL shown in read-only blocks, followed by runnable stand-ins for the ideas that can be reproduced in portable SQL.

Statements, comments and batches

A T-SQL statement is one command. A batch is a group of statements sent to the server together; the server compiles and runs it as a unit. In SSMS and sqlcmd you end a batch with the word GO on its own line. Because GO is a client command, you cannot put it inside a procedure.

Batches matter because variables live only inside one batch, and because some statements (such as CREATE VIEW) must be the first statement in their batch.

Comments come in two forms:

SQL
-- a single-line comment
/* a block comment
   that spans several lines */
SELECT 1; -- end-of-line comment

End statements with a semicolon. It is optional in most places today, but some newer statements (for example a WITH common table expression after another statement) require the previous statement to be terminated.

Variables

Declare a variable with DECLARE, give it a name starting with @ and a data type, and assign with SET or SELECT:

SQL
DECLARE @tax_rate DECIMAL(4,3) = 0.075;   -- declare and initialize (2008+)
DECLARE @price    DECIMAL(10,2);
DECLARE @total    DECIMAL(10,2);

SET @price = 80.00;
SET @total = @price * (1 + @tax_rate);

SELECT @price AS price, @total AS total_with_tax;

SET assigns one value; SELECT @x = column FROM table assigns from a query. If the query returns several rows, SELECT silently keeps the last row, which is a classic source of surprises. If it returns no rows, the variable keeps its old value. For a single value from a table, prefer SET @x = (SELECT ...), which errors if the subquery returns more than one row.

Variables are strongly typed, so DECLARE @n INT = 'abc' fails with a conversion error. Variables are also scoped to the batch: after a GO, @price no longer exists.

SQLite has no variables, but a common-table expression can play the same role for constants. The stand-in below computes the same total.

SQL Stand-in: variables as a one-row CTE

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

SELECT returns a result set that applications can read. PRINT sends a text message to the "Messages" tab, useful for debugging scripts but invisible to most applications:

SQL
DECLARE @name NVARCHAR(50) = N'Ada';
PRINT 'Hello, ' + @name + '!';          -- message
SELECT 'Hello, ' + @name + '!' AS greeting;  -- result set

String concatenation uses + (or CONCAT(), covered in string functions). Note that + with a NULL makes the whole result NULL, while CONCAT treats NULL as empty text.

Decisions: IF ... ELSE and BEGIN ... END

IF runs the next statement when a condition is true. To run several statements, wrap them in BEGIN ... END:

SQL
DECLARE @stock INT = 4;

IF @stock = 0
    PRINT 'Out of stock';
ELSE IF @stock < 5
BEGIN
    PRINT 'Low stock';
    PRINT 'Reorder soon';
END
ELSE
    PRINT 'In stock';

IF controls the flow of a script. Inside a query, to pick a value per row, use the CASE expression instead (see conditional expressions). A frequent pattern is IF EXISTS (SELECT 1 FROM ...), which tests whether a query returns any row. The stand-in below uses CASE, the portable counterpart, over real rows.

SQL Stand-in: IF / ELSE as CASE ยท database: shop

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

Loops: WHILE

T-SQL has one loop, WHILE. Use BREAK to leave it and CONTINUE to jump to the next iteration:

SQL
DECLARE @i INT = 1;
WHILE @i <= 5
BEGIN
    PRINT 'Row number ' + CAST(@i AS VARCHAR(10));
    SET @i += 1;      -- compound assignment: same as SET @i = @i + 1
END

Row-by-row loops are almost always slower than a single set-based statement. SQL Server is built to work on whole sets, so use WHILE for administrative tasks (batching large deletes, iterating over databases), not for ordinary data processing. A loop that builds numbers 1 to 5 can be written as a recursive CTE, which is the portable stand-in:

SQL Stand-in: a counting loop as a recursive CTE

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

Useful system functions and variables

System functions beginning with @@ give you information about the session:

Name Meaning
@@ROWCOUNT Rows affected by the previous statement
@@ERROR Error number of the previous statement (0 = success)
@@IDENTITY Last identity value generated in the session
SCOPE_IDENTITY() Last identity value in the current scope (safer than @@IDENTITY)
@@VERSION Product version text
@@SERVERNAME Name of the server
@@TRANCOUNT Current transaction nesting level

@@ROWCOUNT is reset by every statement, so read it immediately:

SQL
UPDATE dbo.products SET stock = stock + 10 WHERE category_id = 3;
PRINT CONCAT(@@ROWCOUNT, ' rows updated');

Common mistakes

  • Using a variable after GO. It no longer exists; declare it again in the new batch.
  • Reading @@ROWCOUNT after another statement. PRINT or an IF in between resets it.
  • Looping where a set-based query would do. One UPDATE beats a WHILE that updates row by row.
  • Forgetting BEGIN ... END. Only the first statement after IF belongs to it; the second one always runs.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens to a variable declared before a GO line?

    A variable's scope is a single batch. After GO, the next batch starts fresh and must declare the variable again.
  2. 2. Which statement shows text in the Messages tab rather than as a result set?

    PRINT writes a message; SELECT returns rows to the client.
  3. 3. What does SELECT @x = name FROM t return into @x when t has three rows?

    The assignment runs for each row, so the variable ends with the last value processed. SET @x = (SELECT ...) raises an error instead.
  4. 4. Which loop construct does T-SQL provide?

    WHILE is the only loop statement, with BREAK and CONTINUE to control it.

Summary

  • A batch is a unit sent to the server; GO separates batches in client tools.
  • DECLARE @name type creates variables; SET is safest for assigning a single value.
  • PRINT writes messages; SELECT returns result sets.
  • IF ... ELSE and WHILE control script flow; prefer set-based queries over loops.
  • @@ROWCOUNT, SCOPE_IDENTITY() and @@TRANCOUNT report session state.

Next: SQL Server Data Types