SQL While Loop

In this, article we are going to learn about WHILE LOOP in SQL.

What Is While Loop?

The while loop in SQL starts with the WHILE keyword and is followed by a condition that returns a Boolean value, either True or False. Unless the condition returns false, the body of the while loop continues to execute. In SQL, the body of a while loop begins with a BEGIN block and ends with an END block.

Syntax:

WHILE boolean_condition  
BEGIN  
   {SQL_statement | statement_block | BREAK | CONTINUE}  
END;

boolean_condition: It’s a required condition that will be tested in each iteration and return the TRUE or FALSE result.

sql_statement or statement_block: The SQL statement is defined inside the BEGIN and END. It will be executed in each iteration until the condition becomes FALSE.

Break: It ends the innermost loop instantly, and control flow resumes at the next statement after the loop.

Continue: It jumps to future iteration while not skipping the remaining statements within the loop. Usually, it causes the loop to restart from the start.

Example:

DECLARE @Counter INT //declare a variable
SET @Counter=1 //initializing value
WHILE ( @Counter <= 20) //set condition
BEGIN
    PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter) //print value
    SET @Counter  = @Counter  + 1 //incremented the value of the variable
END

Output:

In the above image, you can see that we must increment the variable’s value after each iteration.

Let’s see another example to read table records with the help of a WHILE LOOP.

DECLARE @Count INT, @TotalCount INT, @CountryName NVARCHAR(100)
SELECT @TotalCount = Count(*) FROM Country
SET @Count=1
WHILE (@Count<=@TotalCount)
BEGIN
  SELECT @CountryName = CountryName FROM Country WHERE CountryId = @Count
  PRINT CONVERT(NVARCHAR,@Count) + '. country name is ' + @CountryName
  SET @Count  = @Count  + 1
END

Output:

In this example, we read the table rows with the help of WHILE LOOP. We can also develop more advanced loops based on our needs.

That’s it.

You can also check, How To Used Multi Select Dropdown Using Angular

Submit a Comment

Your email address will not be published.

Subscribe

Select Categories