Navigation

while loop in ms sql

while loop in ms SQL, while loop in ms sql server, while loop in ms sql server 2012 example, loop in sql server

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.

While Loop In SQL


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
Share

Anjan Kant

Outstanding journey in Microsoft Technologies (ASP.Net, C#, SQL Programming, WPF, Silverlight, WCF etc.), client side technologies AngularJS, KnockoutJS, Javascript, Ajax Calls, Json and Hybrid apps etc. I love to devote free time in writing, blogging, social networking and adventurous life

Post A Comment:

2 comments:

  1. Hi Anjan Kant,

    Thanks for this informative post on while loops in SQL SERVER. Hope you would also like my post on stored procedures Vs UDFs at my technology blog.

    ReplyDelete
    Replies
    1. sure sk! sure I'll go through stored procedures Vs UDFs, can you provide me link.

      Delete