All 7 T-SQL Fundamentals & Syntax questions
More in T-SQL Fundamentals & Syntax
What is T-SQL, and how does it relate to standard SQL? How do you declare and use a variable in T-SQL? What is the difference between a batch and a transaction in T-SQL, and how does the GO keyword relate to batches? What are the differences between the various string functions available in T-SQL, such as SUBSTRING, CONCAT, and TRIM? How do CASE expressions work in T-SQL, and how can they be used to implement conditional logic directly within a SELECT statement? How would you write a T-SQL script that combines variables, control flow, and error handling together to perform a robust, multi step administrative task?
Advertisement
Back to results for “Fundamentals”
SQL Server › T-SQL Fundamentals & Syntax

How do IF and WHILE control flow statements work in T-SQL, and what kinds of tasks are they typically used for?

Intermediate

Answer

IF lets you conditionally execute a block of code only when a specified condition is true, while WHILE repeatedly executes a block of code as long as its condition remains true, and together these constructs let you build procedural logic, such as looping through a set of values or branching behavior based on data conditions, directly within your SQL scripts.
DECLARE @Counter INT = 1;
WHILE @Counter <= 5
BEGIN
  PRINT 'Iteration ' + CAST(@Counter AS VARCHAR);
  SET @Counter = @Counter + 1;
END;
Real-world example A maintenance script uses a WHILE loop to process a batch of records in smaller chunks, checking a condition after each iteration to determine whether to continue processing or stop.

Common follow-ups: When should a WHILE loop be avoided in favor of a set based approach?;Can you use BREAK and CONTINUE inside a WHILE loop in T-SQL?

Cursors;Query Optimization & Plans
All 7 T-SQL Fundamentals & Syntax questions