while loop in ms sql

Introduction

SQL Server While Loop is chunk of the SQL Program. While loop repeats the conditions again and again up to end until the condition is true.To discontinue continuation Break statement is used, to keep loop going on is used Continue Statement.

Control For Loop

WHILE (SELECT AVG(Qty) FROM Item.Qty) < 2500
BEGIN
   // do something here
   IF (SELECT MAX(Cost) FROM Item.Cost) > $500
      BREAK
   ELSE
      CONTINUE
END

Execution of While Loop is fully controlled with the statement Break or Continue. 

Try the following Statements to enjoy more While Loop in SQL Server.

While Loop in MS SQL Server:

DECLARE @COUNT INT
SET @COUNT = 1;
WHILE @COUNT < = 4
BEGIN
 SET @COUNT = @COUNT+1;
 PRINT @COUNT;
END

Result Set:

1

2

3

4

Example of WHILE Loop with BREAK keyword:

DECLARE @Count INT
SET @Count = 1
WHILE (@Count <=5)
BEGIN
PRINT @Count
SET @Count = @Count + 1
IF @Count = 4
BREAK;
END
GO

Result Set

1

2

3

Example of WHILE Loop with CONTINUE and BREAK keywords

DECLARE @Count INT
SET @Count = 1
WHILE (@Count <=5)
BEGIN
PRINT @Count
SET @Count = @Count + 1
CONTINUE;
IF @Count = 4 -- This will never executed
BREAK;
END
GO

Result Set

1

2

3

4

5

Suggested Reading

• How to Schedule Backup in SQL Server 2008? 
• Guid in SQL
• Could not open a connection to SQL Server