T-SQL batches, variables and control flow

So far every script has been a list of queries. T-SQL, SQL Server’s dialect of SQL, is also a small programming language: it has variables, IF and WHILE. They are what stored procedures, functions and triggers are made of, so this lesson comes first.

Variables

DECLARE creates a variable. Its name starts with @, it has a data type, and it can be given a starting value. Without one it starts as NULL.

1DECLARE @MinPrice decimal(10,2) = 10.00;2 3SELECT Name, Price4FROM dbo.Products5WHERE Price >= @MinPrice6ORDER BY Price;

Name                 Price
-------------------  -----
Wood screws (200)    11.90
Paint roller set     12.50
Claw hammer          18.50
Plywood sheet 18 mm  42.00
Masonry paint 10 L   54.00
Cordless drill       89.00

Batches and GO

A batch is the block of text that SSMS sends to SQL Server in one go. GO is not T-SQL at all: SSMS (and sqlcmd) look for lines that say GO, cut the script there, and send the pieces one after another. Each batch is compiled and run on its own, and a variable lives only until the end of its batch:

1DECLARE @City nvarchar(40) = N'Leeds';2SELECT COUNT(*) AS Customers FROM dbo.Customers WHERE City = @City;3GO4SELECT COUNT(*) AS Customers FROM dbo.Customers WHERE City = @City;

Customers
---------
        3

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@City".

Some statements must be the first statement in their batch: CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION and CREATE TRIGGER. That is why the previous lesson put GO after each view. Without it, the whole batch fails to compile, so even the PRINT before the CREATE never runs:

1PRINT N'Creating the view…';2CREATE OR ALTER VIEW dbo.LeedsCustomers3AS4SELECT FirstName, LastName FROM dbo.Customers WHERE City = N'Leeds';

Msg 111, Level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch.

SET or SELECT to assign

There are two ways to put a value into a variable. SET assigns one variable. SELECT can assign several at once, straight from a query:

1DECLARE @Products int, @AvgPrice decimal(10,2);2 3SELECT @Products = COUNT(*), @AvgPrice = AVG(Price)4FROM dbo.Products;5 6PRINT CONCAT(N'Products: ', @Products, N', average price: ', @AvgPrice);

Products: 12, average price: 22.25

PRINT sends a line of text to the Messages tab instead of returning a result set. It is useful for following what a script does. Build the text with CONCAT, because + tries to turn the text into a number as soon as one side is a number:

1DECLARE @Products int = 12;2PRINT N'Products: ' + @Products;

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value 'Products: ' to data type int.

The two forms of assignment behave differently when the query returns no rows, or more than one:

1DECLARE @Name nvarchar(60) = N'(nothing yet)';2 3SELECT @Name = Name FROM dbo.Products WHERE Price > 1000;   -- no rows4SELECT @Name AS AfterSelect;5 6SET @Name = (SELECT Name FROM dbo.Products WHERE Price > 1000);7SELECT @Name AS AfterSet;8 9SET @Name = (SELECT Name FROM dbo.Products WHERE CategoryId = 1);  -- three rows

AfterSelect
-------------
(nothing yet)

AfterSet
--------
NULL

Msg 512, Level 16, State 1, Line 9
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

IF … ELSE

IF runs the next statement only when its condition is true. ELSE gives the other path, and ELSE IF chains more tests:

1DECLARE @ProductId int = 9, @Stock int;2 3SELECT @Stock = Stock FROM dbo.Products WHERE ProductId = @ProductId;4 5IF @Stock = 06    PRINT N'Out of stock: reorder now';7ELSE IF @Stock < 208    PRINT N'Low stock';9ELSE10    PRINT N'In stock';

Out of stock: reorder now

IF controls one statement. Indenting the next line does not make it part of the IF. To run several statements, wrap them in BEGIN … END:

1IF 1 = 02    PRINT N'A: inside the IF';3    PRINT N'B: looks inside, but always runs';4 5IF 1 = 06BEGIN7    PRINT N'C: inside the IF';8    PRINT N'D: inside the IF too';9END;

B: looks inside, but always runs

IF EXISTS (subquery) is the usual way to test for rows. It stops reading as soon as one row is found:

1IF EXISTS (SELECT 1 FROM dbo.Orders WHERE Status = 'Pending')2    SELECT OrderId, CustomerId, OrderDate3    FROM dbo.Orders4    WHERE Status = 'Pending';5ELSE6    PRINT N'No pending orders';

OrderId  CustomerId  OrderDate
-------  ----------  ----------
      8           8  2024-05-18
      9           2  2024-06-01

WHILE loops

WHILE repeats a statement, usually a BEGIN … END block, as long as its condition is true. BREAK leaves the loop and CONTINUE jumps back to the test. This loop walks through the out-of-stock products one at a time, in ProductId order:

1DECLARE @Id int = 0, @Name nvarchar(60);2 3WHILE 1 = 14BEGIN5    SELECT TOP (1) @Id = ProductId, @Name = Name6    FROM dbo.Products7    WHERE ProductId > @Id AND Stock = 08    ORDER BY ProductId;9 10    IF @@ROWCOUNT = 0 BREAK;      -- nothing found: we are done11 12    PRINT CONCAT(N'Reorder #', @Id, N': ', @Name);13END;

Reorder #9: Wall plugs (100)
Reorder #12: Safety goggles

@@ROWCOUNT holds the number of rows the previous statement affected. Here it detects the “no rows” case that would otherwise leave @Id unchanged and loop for ever.

Table variables and temporary tables

A table variable is declared like a variable and used like a table. A temporary table (#name) is a real table in tempdb that lasts until your session ends or you drop it:

1DECLARE @Low TABLE (ProductId int PRIMARY KEY, Name nvarchar(60), Stock int);2 3INSERT INTO @Low (ProductId, Name, Stock)4SELECT ProductId, Name, Stock FROM dbo.Products WHERE Stock < 30;5 6SELECT * FROM @Low ORDER BY Stock;

ProductId  Name                Stock
---------  ------------------  -----
        9  Wall plugs (100)        0
       12  Safety goggles          0
        2  Cordless drill         12
       10  Masonry paint 10 L     25

They look alike but behave differently. A table variable ends with its batch, has no statistics (so the optimizer guesses its row count), and, surprisingly, ignores ROLLBACK:

1DECLARE @Log TABLE (Msg nvarchar(50));2CREATE TABLE #Log (Msg nvarchar(50));3 4BEGIN TRANSACTION;5    INSERT INTO @Log VALUES (N'kept by the table variable');6    INSERT INTO #Log VALUES (N'kept by the temp table');7ROLLBACK;8 9SELECT (SELECT COUNT(*) FROM @Log) AS TableVariableRows,10       (SELECT COUNT(*) FROM #Log) AS TempTableRows;11 12DROP TABLE #Log;

TableVariableRows  TempTableRows
-----------------  -------------
                1              0

1DECLARE @CustomerId int = 2, @Name nvarchar(40), @Orders int;2 3SELECT @Name = FirstName FROM dbo.Customers WHERE CustomerId = @CustomerId;4SELECT @Orders = COUNT(*) FROM dbo.Orders WHERE CustomerId = @CustomerId;5 6IF @Orders = 07    PRINT N'No orders';8ELSE9    PRINT CONCAT(@Name, N' has ', @Orders, N' orders');

Ben has 2 orders