How to execute statements in loops in SQL Server Transact-SQL? How to use WHILE ... loops?

You can use WHILE ... statements to execute statements in loops in Transact-SQL using these syntaxes:

-- Loop with a single statement
WHILE condition statement

-- Loop with a statement block
WHILE condition 
   -- statement_block
   BEGIN
      ... statements ...
   END

When a WHILE ... loop statement is executed, the system will:

• Evaluate the "condition" and decide what to do next.
• If the result of the "condition" is true, the statement or statement_block is executed. Then execution is looped back to the condition to repeat the WHILE ... statement.
• If the result of the "condition" is false, the WHILE ... loop statement is ended.

Here is an example on how to use WHILE ... loop statements:

DECLARE @count INT = 1, @sum INT = 0;
WHILE @count <= 10 
   BEGIN
      SET @sum = @sum + @count;
      SET @count = @count+1;
   END
PRINT 'Sum of 1 to 10: ' + CAST(@sum AS CHAR);

Here is another example of WHILE ... loop statement:

-- Counting number of days in 2000
DECLARE @date DATETIME;
DECLARE @count INT;
SET @date = '2000-01-01'; 
SET @count = 0;
WHILE DATEPART(YEAR, @date) = 2000 
   BEGIN
      SET @count = @count + 1;
      SET @date = DATEADD(DAY, 1, @date);
   END
SELECT @count;
366
-- 2000 is a leap year!

 

⇒Conditional Statements and Loops in SQL Server in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials