Wednesday, August 7, 2013

DO.. WHILE and REPEAT… UNTIL loop in MS SQL

DO.. WHILE and REPEAT… UNTIL loop in MS SQL

Introduction

When I am looking for a forum post related to SQL Server, one of the junior professional is asking for DO…WHILE loop is MS SQL Server. Several people wrote their opinion related to it. Every one is saying to use WHILE loop and some of them suggesting with T-SQL structure of CURSOR with WHILE LOOP.

Obviously when a junior professional is learning MS SQL server, the question in mind arise is there DO… WHILE, REPEIT … UNTILL loop present in MS SQL Server Like C or C++ etc.

No one is answering directly on the forum that is we can use DO… WHILE or REPEIT … UNTILL in MS SQL Server or NOT. If yeas how we can implement them.

DO… WHILE in MS SQL Sever

First we look at the algorithm of DO… WHILE.

SET X 1
DO
  PRINT X
  SET X 1
WHILE X <= 10

Now we try to implement it in MS SQL Server.

DECLARE @X INT=1;
WAY:     --> Here the  DO statement
  PRINT @X;
  SET @X += 1;
IF @X<=10 GOTO WAY; --> Here the WHILE @X<=1

REPEAT… UNTIL

First we look at the algorithm of REPEAT... UNTIL

SET X 1
REPEAT
  PRINT X
  SET X 1
UNTIL X 10

Now we try to implement it in MS SQL Server

DECLARE @X INT 1;
WAY:        -- Here the REPEAT statement
  PRINT @X;
  SET @X += 1;
IF NOT(@X >1 0) GOTO WAY; -- Here the  UNTIL @X>10

So we see that it is possible, but little complicated… So most of the developer prefers the WHILE loop in MS SQL Server.