Learn While loop in SQL

The looping statement repeatedly executes command in its block until the specified condition become true or it terminates by break keyword.
SQL extensively use While loop statement to iterate the PL/SQL statement because this is the only looping statement support by SQL.
IN this article we are going to learn to how to use of While Loop, Break Keyword, and Continue keyword in sql.

While Loop: The statement in code block execute repeatedly until condition specified in Boolean expression become satisfied or it terminate by break keyword. Here is the basic syntax of while loop.

WHILE Boolean_expression
begin
  {code_block | BREAK | CONTINUE }
end

Example: This example show Multiplication of tables. I will increment the count from 1 to 10 and then do multiplication of count with specific number. Let’s say table of 2

DECLARE
 @tableof tinyint =2,
 @count tinyint

SELECT @count = 1
WHILE @count <= 10
BEGIN
  print  cast(@tableof as varchar(2))+ ' * ' +  cast(@count as varchar(10)) + ' = '  +  cast( @tableof * @count as varchar(10))  
  set @count = @count +1
END

The above looping statement gives below results

2 * 1 = 2
2 * 2 = 4
2 * 3 = 6
2 * 4 = 8
2 * 5 = 10
2 * 6 = 12
2 * 7 = 14
2 * 8 = 16
2 * 9 = 18
2 * 10 = 20

Break-Keyword: It stops the execution of while loop or it terminates the execution process of while loop

Example: in this example I am terminating the execution of statement when count values reach to 5

DECLARE
 @tableof tinyint =2,
 @count tinyint

SELECT @count = 1
WHILE @count <= 10
BEGIN
  print  '2  * ' + cast (@count as varchar(2))  +' = ' +  cast( @tableof * @count as varchar(10))  
  set @count = @count +1

  if @count =5
  break;   -- the loop will terminate the execution when @count reach to 5
END

According the above looping statement it gives following result:

Continue- keyword: It skips the execution of all the statement after its execution and start execution from first statement of while loop.

DECLARE
 @tableof tinyint =2,
 @count tinyint

SELECT @count = 1
WHILE @count <= 10
BEGIN
  print  '2  * ' + cast (@count as varchar(2))  +' = ' +  cast( @tableof * @count as varchar(10))  
  set @count = @count +1
  continue     -- The control of execution move to first statement of loop when it reach to continue keyword
  if @count =5 -- This statement will skip from execution and will never execute
  break;  
END

This will give following result after execution

2  * 1 = 2
2  * 2 = 4
2  * 3 = 6
2  * 4 = 8
2  * 5 = 10
2  * 6 = 12
2  * 7 = 14
2  * 8 = 16
2  * 9 = 18
2  * 10 = 20

Example : In this example I am going to update the salary table . I will update the basic pay to increment 2000 to each employee as per bonus
Let first look on the salary table:

We will make loop to update basic pay as 2000 increment for each employee.

declare @totalrecord int
declare @step int =1
select @totalrecord = count(*) from tblsalary

while @step <= @totalrecord
begin

update tblsalary set pay = pay +2000 where empid =@step


set @step =@step + 1
end

After execution of above looping statement , we can see that basic pay is hike with 2000 for each employee

Hence, we have learn the While loop in SQL.