T-SQL Programming Fundamentals

18.1 Batches and GO

A batch is a group of statements sent together. GO is a client batch separator, not T-SQL.

CREATE PROCEDURE/VIEW/FUNCTION/TRIGGER must be the first statement in a batch.

GO 5 repeats the batch 5 times (in SSMS and sqlcmd).

18.2 Variables

SET raises an error if the subquery returns more than one row. SELECT silently assigns the last value.

DECLARE @Name NVARCHAR(50) = N'Asha', @Count INT, @Today DATE = GETDATE();
SET @Count = (SELECT COUNT(*) FROM dbo.Customers);
SELECT @Count = COUNT(*), @Name = MAX(FirstName) FROM dbo.Customers;  -- multiple assignment
PRINT CONCAT('Customers: ', @Count);
SELECT @Count AS CustomerCount;

18.3 Control of Flow

-- IF...ELSE
IF EXISTS (SELECT 1 FROM dbo.Products WHERE Stock = 0)
BEGIN
    PRINT 'Some products are out of stock';
END
ELSE
    PRINT 'All in stock';

-- WHILE with BREAK / CONTINUE
DECLARE @i INT = 1;
WHILE @i <= 10
BEGIN
    IF @i % 2 = 0 BEGIN SET @i += 1; CONTINUE; END
    PRINT @i;
    IF @i = 7 BREAK;
    SET @i += 1;
END

-- WAITFOR
WAITFOR DELAY '00:00:05';
WAITFOR TIME '23:00';

-- GOTO (rarely used)
GOTO Done;  ... Done: PRINT 'Done';

-- RETURN exits the batch/procedure

18.4 SET Options that Matter

SET NOCOUNT ON;              -- suppress "(n rows affected)" messages
SET XACT_ABORT ON;           -- abort transaction on runtime error
SET ANSI_NULLS ON;           -- standard NULL comparison
SET QUOTED_IDENTIFIER ON;    -- double quotes = identifiers
SET STATISTICS IO, TIME ON;  -- performance info
SET LOCK_TIMEOUT 5000;       -- ms
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET ROWCOUNT 0;              -- deprecated for DML
SET LANGUAGE us_english;

18.5 Printing and Messages

PRINT 'Message';
RAISERROR('Progress: %d%%', 0, 1, 50) WITH NOWAIT;   -- immediate message (severity 0)

18.6 Set-Based vs Row-by-Row (RBAR)

SQL is a declarative, set-based language. Prefer one set-based statement over loops and cursors.

Loops are fine for admin tasks and batching large modifications.