SQL Server 2008에서 while 루프 수행

SQL Server 2008에서 while 루프 수행

---

do whileSQL Server 2008에서 루프 를 구현하는 방법이 있습니까?

---

MS SQL Server 2008의 DO-WHILE에 대해 잘 모르겠지만 DO-WHILE 루프와 같이 사용하도록 WHILE 루프 논리를 변경할 수 있습니다.

예는 다음과 같습니다. http://blog.sqlauthority.com/2007/10/24/sql-server-simple-example-of-while-loop-with-continue-and-break-keywords/

1. WHILE 루프의 예

   DECLARE @intFlag INT
   SET @intFlag = 1
   WHILE (@intFlag <=5)
   BEGIN
       PRINT @intFlag
       SET @intFlag = @intFlag + 1
   END
   GO
   

   결과 세트 :

   1
   2
   3
   4
   5
   

2. 키워드가 BREAK 인 WHILE 루프의 예

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

   결과 세트 :

   1
   2
   3
   

3. CONTINUE 및 BREAK 키워드가있는 WHILE 루프의 예

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

   결과 세트 :

   1
   2
   3
   4
   5
   

그러나 데이터베이스 수준에서 루프 를 피하십시오 . 참조 .

---

GOTO키워드에 크게 영향을받지 않으면 DO/ WHILEin T-SQL 을 시뮬레이션하는 데 사용할 수 있습니다 . 의사 코드로 작성된 다음과 같은 무의미한 예를 고려하십시오.

SET I=1
DO
 PRINT I
 SET I=I+1
WHILE I<=10

Here is the equivalent T-SQL code using goto:

DECLARE @I INT=1;
START:                -- DO
  PRINT @I;
  SET @I+=1;
IF @I<=10 GOTO START; -- WHILE @I<=10

Notice the one to one mapping between the GOTO enabled solution and the original DO / WHILE pseudocode. A similar implementation using a WHILE loop would look like:

DECLARE @I INT=1;
WHILE (1=1)              -- DO
 BEGIN
  PRINT @I;
  SET @I+=1;
  IF NOT (@I<=10) BREAK; -- WHILE @I<=10
 END

Now, you could of course rewrite this particular example as a simple WHILE loop, since this is not such a good candidate for a DO / WHILE construct. The emphasis was on example brevity rather than applicability, since legitimate cases requiring a DO / WHILE are rare.

---

REPEAT / UNTIL, anyone (does NOT work in T-SQL)?

SET I=1
REPEAT
  PRINT I
  SET I=I+1
UNTIL I>10

... and the GOTO based solution in T-SQL:

DECLARE @I INT=1;
START:                    -- REPEAT
  PRINT @I;
  SET @I+=1;
IF NOT(@I>10) GOTO START; -- UNTIL @I>10

Through creative use of GOTO and logic inversion via the NOT keyword, there is a very close relationship between the original pseudocode and the GOTO based solution. A similar solution using a WHILE loop looks like:

DECLARE @I INT=1;
WHILE (1=1)       -- REPEAT
 BEGIN
  PRINT @I;
  SET @I+=1;
  IF @I>10 BREAK; -- UNTIL @I>10
 END

An argument can be made that for the case of the REPEAT / UNTIL, the WHILE based solution is simpler, because the if condition is not inverted. On the other hand it is also more verbose.

If it wasn't for all of the disdain around the use of GOTO, these might even be idiomatic solutions for those few times when these particular (evil) looping constructs are necessary in T-SQL code for the sake of clarity.

Use these at your own discretion, trying not to suffer the wrath of your fellow developers when they catch you using the much maligned GOTO.

---

I seem to recall reading this article more than once, and the answer is only close to what I need.

Usually when I think I'm going to need a DO WHILE in T-SQL it's because I'm iterating a cursor, and I'm looking largely for optimal clarity (vs. optimal speed). In T-SQL that seems to fit a WHILE TRUE / IF BREAK.

If that's the scenario that brought you here, this snippet may save you a moment. Otherwise, welcome back, me. Now I can be certain I've been here more than once. :)

DECLARE Id INT, @Title VARCHAR(50)
DECLARE Iterator CURSOR FORWARD_ONLY FOR
SELECT Id, Title FROM dbo.SourceTable
OPEN Iterator
WHILE 1=1 BEGIN
    FETCH NEXT FROM @InputTable INTO @Id, @Title
    IF @@FETCH_STATUS < 0 BREAK
    PRINT 'Do something with ' + @Title
END
CLOSE Iterator
DEALLOCATE Iterator

Unfortunately, T-SQL doesn't seem to offer a cleaner way to singly-define the loop operation, than this infinite loop.

---

You can also use an exit variable if you want your code to be a bit more readable:

DECLARE @Flag int = 0
DECLARE @Done bit = 0

WHILE @Done = 0 BEGIN
    SET @Flag = @Flag + 1
    PRINT @Flag
    IF @Flag >= 5 SET @Done = 1
END

This would probably be more relevant when you have a more complicated loop and are trying to keep track of the logic. As stated loops are expensive so try and use other methods if you can.

---

Only While Loop is officially supported by SQL server. Already there is answer for DO while loop. I am detailing answer on ways to achieve different types of loops in SQL server.

If you know, you need to complete first iteration of loop anyway, then you can try DO..WHILE or REPEAT..UNTIL version of SQL server.

DO..WHILE Loop

DECLARE @X INT=1;

WAY:  --> Here the  DO statement

  PRINT @X;

  SET @X += 1;

IF @X<=10 GOTO WAY;

REPEAT..UNTIL Loop

DECLARE @X INT = 1;

WAY:  -- Here the REPEAT statement

  PRINT @X;

  SET @X += 1;

IFNOT(@X > 10) GOTO WAY;

FOR Loop

DECLARE @cnt INT = 0;

WHILE @cnt < 10
BEGIN
   PRINT 'Inside FOR LOOP';
   SET @cnt = @cnt + 1;
END;

PRINT 'Done FOR LOOP';

Reference

참고URL : https://stackoverflow.com/questions/4487546/do-while-loop-in-sql-server-2008