How to do for loop in SQL SERVER?

Question

BEGIN
 <<LOOP_EXTERNO>>
 FOR V_EXTERNO IN 1..20 LOOP
  DBMS_OUTPUT.PUT_LINE('Contador externo: ' || V_EXTERNO);
  <<LOOP_INTERNO>>
   FOR V_INTERNO IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Contador interno: ' || V_INTERNO);
    IF V_EXTERNO = 10 THEN
     EXIT LOOP_EXTERNO;
    END IF;
  END LOOP INTERNO;
 END LOOP EXTERNO;
END;
/

This command is in the PL/SQL, and I don’t understand how to do it in SQL server, I don’t know if it’s the same thing or something changes.

Could someone help me?

Answer 1

sql server does not have the FOR LOOP, instead he use the WHILE , for this you have to create variables as counter.

Your code look like this.

declare @V_EXTERNO int = 1
declare @V_INTERNO int = 1

BEGIN
    WHILE @V_EXTERNO < 21   
    BEGIN
        print 'Contador externo: ' + cast(@V_EXTERNO as varchar(10));
        WHILE @V_INTERNO < 6        
        BEGIN
             print 'Contador interno: ' + cast(@V_INTERNO as varchar(10));
             SET @V_INTERNO = @V_INTERNO + 1;
        END;
       SET @V_EXTERNO = @V_EXTERNO + 1;
       SET @V_INTERNO = 1;
    END;
END;

Answer 2

In the SQL SERVER there is no FOR LOOP, you must simulate it using WHILE LOOP.

Basic syntax;

DECLARE @cnt INT = 0;

WHILE @cnt < cnt_total
BEGIN
   {...statements...}
   SET @cnt = @cnt + 1;
END;

cnt_total; The number of times you want WHILE LOOP to run.
statements; Code declarations that will be executed every time WHILE LOOP is passed.

See this example below;

DECLARE @cnt INT = 0;

WHILE @cnt < 10
BEGIN
   PRINT 'StackOverflow';
   SET @cnt = @cnt + 1;
END;

PRINT 'Feito';
GO