Mastering T-SQL essentials—variables, control flow, and transactions—is fundamental for every SQL developer who wants to write dynamic, intelligent database code. These T-SQL programming concepts transform SQL from a simple query language into a complete programming environment where you can store values, make decisions, create loops, and ensure data integrity. In this comprehensive guide, I'll explain T-SQL variables, control flow statements, and transactions using practical examples from the WideWorldImporters database, helping you understand how to write procedural T-SQL code that solves real-world problems.
Understanding T-SQL essentials opens the door to writing stored procedures, complex scripts, and automation routines. These foundational concepts are essential for any developer working with SQL Server, whether you're building applications, performing data analysis, or managing databases.
Understanding T-SQL Variables
Variables in T-SQL are temporary storage locations that hold data during script execution. They allow you to store query results, perform calculations, and pass values between statements.
Developer Insight: When I first learned variables, I thought they were only for stored procedures. Then I discovered how powerful they are for testing queries, building dynamic SQL, and debugging complex logic. Variables make T-SQL feel like a real programming language.
Declaring Variables
Variables must be declared before use with the DECLARE statement:
-- Declare a single variable
DECLARE @CustomerID INT;
-- Declare multiple variables at once
DECLARE
@CustomerName NVARCHAR(100),
@OrderCount INT,
@TotalRevenue DECIMAL(18,2);
Variable names must start with the @ symbol. Choose descriptive names that indicate their purpose.
Setting Variable Values
You can assign values using SET or SELECT:
-- Using SET (assigns one value at a time)
DECLARE @CustomerID INT;
SET @CustomerID = 1;
-- Using SELECT (can assign multiple variables at once)
DECLARE @OrderDate DATE, @CustomerName NVARCHAR(100);
SELECT
@OrderDate = OrderDate,
@CustomerName = c.CustomerName
FROM Sales.Orders o
INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderID = 1;
-- Display variable values
SELECT @CustomerID AS CustomerID, @OrderDate AS OrderDate, @CustomerName AS CustomerName;
Key Difference: SET requires exactly one value (fails with multiple rows), while SELECT takes the last value if multiple rows match.
Using Variables in Queries
Variables make queries dynamic and reusable:
-- Declare and set a customer ID
DECLARE @TargetCustomerID INT = 832;
-- Use the variable in queries
SELECT
o.OrderID,
o.OrderDate,
o.ExpectedDeliveryDate
FROM Sales.Orders o
WHERE o.CustomerID = @TargetCustomerID
ORDER BY o.OrderDate DESC;
-- Use in calculations
DECLARE @StartDate DATE = '2016-05-01';
DECLARE @EndDate DATE = '2016-05-31';
SELECT
COUNT(*) AS OrderCount,
SUM(ol.Quantity * ol.UnitPrice) AS TotalRevenue
FROM Sales.Orders o
INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
WHERE o.OrderDate BETWEEN @StartDate AND @EndDate;
System Variables
SQL Server provides built-in global variables (identified by @@):
-- @@ROWCOUNT: Number of rows affected by last statement
UPDATE Warehouse.StockItems
SET UnitPrice = UnitPrice * 1.05
WHERE StockItemID = 1;
SELECT @@ROWCOUNT AS RowsAffected;
-- @@ERROR: Error number from last statement
SELECT @@ERROR AS LastError;
-- @@IDENTITY: Last identity value inserted
-- @@TRANCOUNT: Number of active transactions
SELECT @@TRANCOUNT AS ActiveTransactions;
-- @@SERVERNAME: Name of SQL Server instance
SELECT @@SERVERNAME AS ServerName;
Developer Insight: I constantly use @@ROWCOUNT to verify that UPDATE and DELETE statements affected the expected number of rows. It's an essential debugging tool.
Understanding Control Flow in T-SQL
Control flow statements allow you to make decisions, repeat operations, and control execution order—just like in traditional programming languages.
IF...ELSE: Conditional Execution
IF evaluates a condition and executes code based on the result:
-- Simple IF statement
DECLARE @OrderCount INT;
SELECT @OrderCount = COUNT(*)
FROM Sales.Orders
WHERE CustomerID = 1;
IF @OrderCount > 50
PRINT 'Customer is a VIP with over 50 orders';
ELSE
PRINT 'Customer is a regular customer';
IF...ELSE with BEGIN...END Blocks
Use BEGIN...END to execute multiple statements:
-- Multiple statements require BEGIN...END
DECLARE @StockItemID INT = 1;
DECLARE @Quantity INT;
SELECT @Quantity = QuantityOnHand
FROM Warehouse.StockItemHoldings
WHERE StockItemID = @StockItemID;
IF @Quantity < 10
BEGIN
PRINT 'CRITICAL: Low stock level detected';
PRINT 'Stock Item ID: ' + CAST(@StockItemID AS VARCHAR(10));
PRINT 'Current Quantity: ' + CAST(@Quantity AS VARCHAR(10));
-- In production, you might send an alert here
END
ELSE
BEGIN
PRINT 'Stock levels are adequate';
PRINT 'Current Quantity: ' + CAST(@Quantity AS VARCHAR(10));
END;
Common Mistake: Forgetting BEGIN...END when you have multiple statements. Only the first statement after IF executes without the block.
Nested IF Statements
Create complex decision trees with nested IF:
-- Categorize customers by order activity
DECLARE @CustomerID INT = 832;
DECLARE @OrderCount INT;
DECLARE @LastOrderDate DATE;
SELECT
@OrderCount = COUNT(*),
@LastOrderDate = MAX(OrderDate)
FROM Sales.Orders
WHERE CustomerID = @CustomerID;
IF @OrderCount = 0
PRINT 'New customer - no orders yet';
ELSE IF @OrderCount < 10
BEGIN
IF DATEDIFF(DAY, @LastOrderDate, GETDATE()) > 180
PRINT 'Occasional customer - inactive';
ELSE
PRINT 'Occasional customer - active';
END
ELSE IF @OrderCount < 50
PRINT 'Regular customer';
ELSE
PRINT 'VIP customer';
WHILE Loops: Repeated Execution
WHILE repeats code as long as a condition is true:
-- Simple WHILE loop
DECLARE @Counter INT = 1;
WHILE @Counter <= 5
BEGIN
PRINT 'Iteration: ' + CAST(@Counter AS VARCHAR(10));
SET @Counter = @Counter + 1;
END;
Practical WHILE Example: Processing Records
-- Process stock items one by one
DECLARE @CurrentItemID INT;
DECLARE @MaxItemID INT;
SELECT @CurrentItemID = MIN(StockItemID), @MaxItemID = MAX(StockItemID)
FROM Warehouse.StockItems
WHERE StockItemID <= 10; -- Limit for demo
WHILE @CurrentItemID <= @MaxItemID
BEGIN
-- Process each stock item
DECLARE @ItemName NVARCHAR(100);
DECLARE @CurrentStock INT;
SELECT
@ItemName = si.StockItemName,
@CurrentStock = sih.QuantityOnHand
FROM Warehouse.StockItems si
INNER JOIN Warehouse.StockItemHoldings sih ON si.StockItemID = sih.StockItemID
WHERE si.StockItemID = @CurrentItemID;
PRINT 'Item: ' + @ItemName + ' - Stock: ' + CAST(@CurrentStock AS VARCHAR(10));
-- Move to next item
SELECT @CurrentItemID = MIN(StockItemID)
FROM Warehouse.StockItems
WHERE StockItemID > @CurrentItemID AND StockItemID <= 10;
END;
PRINT 'Processing complete';
Developer Insight: WHILE loops are powerful but can be slow on large datasets. When possible, use set-based operations instead. I reserve WHILE for administrative tasks, not data processing.
BREAK and CONTINUE in Loops
Control loop execution with BREAK and CONTINUE:
-- BREAK exits the loop immediately
DECLARE @Counter INT = 1;
WHILE @Counter <= 100
BEGIN
IF @Counter = 5
BREAK; -- Exit loop when counter reaches 5
PRINT 'Counter: ' + CAST(@Counter AS VARCHAR(10));
SET @Counter = @Counter + 1;
END;
PRINT 'Loop ended at: ' + CAST(@Counter AS VARCHAR(10));
-- CONTINUE skips to next iteration
DECLARE @Counter INT = 0;
WHILE @Counter < 10
BEGIN
SET @Counter = @Counter + 1;
IF @Counter % 2 = 0
CONTINUE; -- Skip even numbers
PRINT 'Odd number: ' + CAST(@Counter AS VARCHAR(10));
END;
TRY...CATCH: Error Handling
Catch and handle errors gracefully:
-- Basic TRY...CATCH structure
BEGIN TRY
-- Code that might cause an error
DECLARE @Result INT;
SET @Result = 10 / 0; -- Division by zero error
END TRY
BEGIN CATCH
-- Handle the error
PRINT 'Error occurred!';
PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
PRINT 'Error Message: ' + ERROR_MESSAGE();
PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10));
END CATCH;
Practical TRY...CATCH Example
-- Safe data modification with error handling
BEGIN TRY
-- Attempt to update stock item price
DECLARE @ItemID INT = 1;
DECLARE @NewPrice DECIMAL(18,2) = 29.99;
UPDATE Warehouse.StockItems
SET UnitPrice = @NewPrice,
LastEditedWhen = SYSDATETIME()
WHERE StockItemID = @ItemID;
IF @@ROWCOUNT = 0
THROW 50001, 'Stock item not found', 1;
PRINT 'Price updated successfully';
END TRY
BEGIN CATCH
PRINT 'Update failed: ' + ERROR_MESSAGE();
-- Log error to a table in production
-- INSERT INTO ErrorLog (ErrorMessage, ErrorTime)
-- VALUES (ERROR_MESSAGE(), GETDATE());
END CATCH;
Developer Insight: Always wrap data modifications in TRY...CATCH blocks. In production, I log errors to a table instead of just printing them. This creates an audit trail for troubleshooting.
Understanding Transactions in T-SQL
Transactions ensure data integrity by treating multiple operations as a single atomic unit—either all succeed or all fail together.
Basic Transaction Syntax
BEGIN TRANSACTION; -- or BEGIN TRAN
-- Your SQL statements here
UPDATE Table1 SET Column = Value WHERE Condition;
INSERT INTO Table2 VALUES (...);
COMMIT TRANSACTION; -- or COMMIT (saves changes)
-- or ROLLBACK TRANSACTION (undoes changes)
Simple Transaction Example
-- Transfer inventory between locations
BEGIN TRANSACTION;
-- Declare variables
DECLARE @SourceItemID INT = 1;
DECLARE @TransferQty INT = 10;
-- Check if source has enough stock
DECLARE @CurrentStock INT;
SELECT @CurrentStock = QuantityOnHand
FROM Warehouse.StockItemHoldings
WHERE StockItemID = @SourceItemID;
IF @CurrentStock >= @TransferQty
BEGIN
-- Reduce stock
UPDATE Warehouse.StockItemHoldings
SET QuantityOnHand = QuantityOnHand - @TransferQty
WHERE StockItemID = @SourceItemID;
PRINT 'Transfer complete';
COMMIT TRANSACTION;
END
ELSE
BEGIN
PRINT 'Insufficient stock - transaction cancelled';
ROLLBACK TRANSACTION;
END;
Transaction with Error Handling
Combine transactions with TRY...CATCH for robust data operations:
-- Create order with automatic rollback on error
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @CustomerID INT = 1;
DECLARE @OrderID INT;
-- Validate customer exists
IF NOT EXISTS (SELECT 1 FROM Sales.Customers WHERE CustomerID = @CustomerID)
THROW 50001, 'Customer does not exist', 1;
-- Create order
INSERT INTO Sales.Orders (CustomerID, OrderDate, ExpectedDeliveryDate)
VALUES (@CustomerID, GETDATE(), DATEADD(DAY, 7, GETDATE()));
SET @OrderID = SCOPE_IDENTITY();
-- Add order lines
INSERT INTO Sales.OrderLines (OrderID, StockItemID, Quantity, UnitPrice)
VALUES
(@OrderID, 1, 5, 10.00),
(@OrderID, 2, 3, 15.00);
-- All successful - commit
COMMIT TRANSACTION;
PRINT 'Order created successfully. Order ID: ' + CAST(@OrderID AS VARCHAR(10));
END TRY
BEGIN CATCH
-- Error occurred - rollback everything
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT 'Order creation failed: ' + ERROR_MESSAGE();
END CATCH;
Checking Transaction State
Use @@TRANCOUNT to check if you're in a transaction:
-- Check transaction nesting level
SELECT @@TRANCOUNT AS ActiveTransactions;
BEGIN TRANSACTION;
SELECT @@TRANCOUNT AS ActiveTransactions; -- Returns 1
BEGIN TRANSACTION;
SELECT @@TRANCOUNT AS ActiveTransactions; -- Returns 2 (nested)
COMMIT;
SELECT @@TRANCOUNT AS ActiveTransactions; -- Returns 1
COMMIT;
SELECT @@TRANCOUNT AS ActiveTransactions; -- Returns 0
Practical Real-World Example: Complete Script
Combining variables, control flow, and transactions:
-- Automated stock reorder process
BEGIN TRY
BEGIN TRANSACTION;
-- Variables
DECLARE @ProcessDate DATETIME2 = SYSDATETIME();
DECLARE @ReorderCount INT = 0;
DECLARE @ErrorCount INT = 0;
PRINT 'Starting reorder process at ' + CAST(@ProcessDate AS VARCHAR(30));
-- Find items needing reorder
DECLARE @ItemID INT;
DECLARE @ItemName NVARCHAR(100);
DECLARE @CurrentStock INT;
DECLARE @ReorderLevel INT;
DECLARE @ReorderQty INT;
-- Cursor to process each low-stock item
DECLARE reorder_cursor CURSOR FOR
SELECT
si.StockItemID,
si.StockItemName,
sih.QuantityOnHand,
sih.ReorderLevel,
(sih.ReorderLevel * 2) - sih.QuantityOnHand AS OrderQuantity
FROM Warehouse.StockItems si
INNER JOIN Warehouse.StockItemHoldings sih ON si.StockItemID = sih.StockItemID
WHERE sih.QuantityOnHand < sih.ReorderLevel
AND si.StockItemID <= 10; -- Limit for demo
OPEN reorder_cursor;
FETCH NEXT FROM reorder_cursor INTO @ItemID, @ItemName, @CurrentStock, @ReorderLevel, @ReorderQty;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Processing: ' + @ItemName;
PRINT ' Current Stock: ' + CAST(@CurrentStock AS VARCHAR(10));
PRINT ' Reorder Level: ' + CAST(@ReorderLevel AS VARCHAR(10));
PRINT ' Order Quantity: ' + CAST(@ReorderQty AS VARCHAR(10));
-- In production, you'd create purchase orders here
-- For demo, just count the reorders
SET @ReorderCount = @ReorderCount + 1;
FETCH NEXT FROM reorder_cursor INTO @ItemID, @ItemName, @CurrentStock, @ReorderLevel, @ReorderQty;
END;
CLOSE reorder_cursor;
DEALLOCATE reorder_cursor;
-- Summary
PRINT '';
PRINT 'Reorder process complete';
PRINT 'Items processed: ' + CAST(@ReorderCount AS VARCHAR(10));
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
IF CURSOR_STATUS('global', 'reorder_cursor') >= 0
BEGIN
CLOSE reorder_cursor;
DEALLOCATE reorder_cursor;
END;
PRINT 'ERROR: ' + ERROR_MESSAGE();
END CATCH;
Common Mistakes to Avoid
Mistake 1: Forgetting @ Symbol for Variables
Problem: Using variables without @ causes errors.
-- WRONG
DECLARE CustomerID INT;
-- CORRECT
DECLARE @CustomerID INT;
Mistake 2: Not Using BEGIN...END with Multiple Statements
Problem: Only first statement executes after IF without BEGIN...END.
-- WRONG: Second PRINT always executes
IF 1 = 0
PRINT 'This will not print';
PRINT 'This WILL print - not part of IF!';
-- CORRECT: Use BEGIN...END block
IF 1 = 0
BEGIN
PRINT 'This will not print';
PRINT 'This will not print either';
END;
Mistake 3: Infinite WHILE Loops
Problem: Forgetting to update loop condition.
-- WRONG: Infinite loop!
DECLARE @Counter INT = 1;
WHILE @Counter <= 10
BEGIN
PRINT @Counter;
-- Forgot to increment @Counter!
END;
-- CORRECT: Increment the counter
DECLARE @Counter INT = 1;
WHILE @Counter <= 10
BEGIN
PRINT @Counter;
SET @Counter = @Counter + 1; -- Must increment!
END;
Mistake 4: Not Rolling Back on Errors
Problem: Leaving transactions open when errors occur.
-- WRONG: Transaction stays open on error
BEGIN TRANSACTION;
UPDATE Table1 SET Column = Value;
-- Error occurs here, transaction never committed or rolled back
-- CORRECT: Always handle errors in transactions
BEGIN TRY
BEGIN TRANSACTION;
UPDATE Table1 SET Column = Value;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCH;
Mistake 5: Using SET with Multi-Row Results
Problem: SET fails if query returns multiple rows.
-- WRONG: Error if multiple customers exist
DECLARE @Name NVARCHAR(100);
SET @Name = (SELECT CustomerName FROM Sales.Customers);
-- CORRECT: Use SELECT for potential multiple rows
DECLARE @Name NVARCHAR(100);
SELECT @Name = CustomerName FROM Sales.Customers WHERE CustomerID = 1;
Best Practices for T-SQL Essentials
Variables:
- Use descriptive names (@CustomerID not @x)
- Declare variables at the start of batches
- Initialize variables when declaring when possible
- Use appropriate data types to avoid conversions
Control Flow:
- Always use BEGIN...END for multiple statements
- Keep IF conditions simple and readable
- Avoid deep nesting (max 3-4 levels)
- Prefer set-based operations over WHILE loops
- Add comments explaining business logic
Transactions:
- Keep transactions short
- Always use TRY...CATCH with transactions
- Check @@TRANCOUNT before ROLLBACK
- Commit or rollback explicitly—don't leave transactions open
- Handle all possible error conditions
Summary and Key Takeaways
Mastering T-SQL essentials is crucial for professional SQL development. Here's what you need to remember:
Variables store data temporarily:
- Declare with DECLARE @VariableName DataType
- Assign with SET or SELECT
- Use @ prefix for user variables
- Use @@ prefix for system variables
- Check @@ROWCOUNT, @@ERROR for operation results
Control Flow directs execution:
- IF...ELSE: Conditional execution
- WHILE: Repeated execution (loops)
- BEGIN...END: Group multiple statements
- BREAK/CONTINUE: Control loops
- TRY...CATCH: Error handling
Transactions ensure data integrity:
- BEGIN TRANSACTION starts atomic operation
- COMMIT saves all changes
- ROLLBACK undoes all changes
- Always combine with TRY...CATCH
- Check @@TRANCOUNT for transaction state
Best Practices:
- Use descriptive variable names
- Handle errors with TRY...CATCH
- Keep transactions short
- Prefer set-based operations over loops
- Always rollback transactions on errors
Developer Insight: These T-SQL essentials transform SQL from a query language into a complete programming environment. The WideWorldImporters database provides excellent practice opportunities. Start with simple scripts using variables, progress to control flow, then add transactions for data safety. Every professional stored procedure uses these concepts—mastering them is essential.
Next Steps: Practice writing scripts that combine these concepts. Create stored procedures that accept parameters (variables), make decisions (IF...ELSE), process data safely (transactions), and handle errors (TRY...CATCH). The more you practice, the more natural T-SQL programming becomes.
Frequently Asked Questions
Q: What's the difference between SET and SELECT when assigning variable values?
A: SET assigns one variable at a time and fails if the query returns multiple rows or no rows (assigning NULL). SELECT can assign multiple variables simultaneously and takes the last value if multiple rows match. Use SET for single assignments and when you need strict control. Use SELECT when assigning from query results or assigning multiple variables at once. For safety with potential multi-row results, use SELECT TOP 1 or ensure your WHERE clause returns exactly one row.
Q: Do I always need BEGIN...END with IF statements?
A: Only if you want to execute multiple statements as part of the IF condition. If you have just one statement after IF or ELSE, BEGIN...END is optional but recommended for clarity. However, with multiple statements, BEGIN...END is required—otherwise, only the first statement is considered part of the IF block. Many developers always use BEGIN...END even for single statements to prevent future bugs when code is modified.
Q: When should I use transactions, and what happens if I don't commit?
A: Use transactions whenever you have multiple related data modifications that must succeed or fail together—like transferring money between accounts or creating an order with order lines. If you don't explicitly COMMIT or ROLLBACK, SQL Server's behavior depends on the connection settings. In SSMS with autocommit enabled, implicit transactions commit automatically. In explicit transaction mode or when connection closes unexpectedly, transactions rollback. Always explicitly COMMIT or ROLLBACK for predictable behavior, and always wrap transactions in TRY...CATCH to handle errors properly.