FOR Statement - Range Loop - Informix to SQL Server Migration

In Informix, you can use the FOR statement to execute a loop with the specified number of iterations.

In SQL Server, you can use the WHILE statement, evaluate and increment the counter explicitly.

Informix:

  CREATE PROCEDURE loop1()
    -- Loop variable has to be declared
    DEFINE i INTEGER;  
 
    -- Run a loop
    FOR i = 1 TO 10
      INSERT INTO items VALUES(i);
 
      -- Leave the loop
      IF i = 5 THEN
        EXIT FOR;
      END IF;
 
    END FOR;
  END PROCEDURE;

SQL Server:

  CREATE PROCEDURE loop1 
  AS 
    DECLARE @i INTEGER;  
 
    -- Run a loop
    SET @i = 1;
    WHILE @i <= 10 BEGIN
      INSERT INTO items VALUES(@i);
 
      -- Leave the loop
      IF @i = 5 BEGIN
        BREAK;
      END 
 
      -- Increment the loop counter
      SET @i = @i + 1;
    END
  GO

Using STEP to Specify Increment

By default, the loop counter increases by 1 with each iteration, but you can specify a different increment using the STEP clause:

Informix:

  CREATE PROCEDURE loop2()
    -- Loop variable has to be declared
    DEFINE i INTEGER;  
 
    -- Run a loop
    FOR i = 1 TO 10 STEP 2
      INSERT INTO items VALUES(i);
 
      -- Leave the loop
      IF i > 5 THEN
        EXIT FOR;
      END IF;
 
    END FOR;
  END PROCEDURE;

SQL Server:

  CREATE PROCEDURE loop2 
  AS 
    DECLARE @i INTEGER;  
 
    -- Run a loop
    SET @i = 1;
    WHILE @i <= 10 BEGIN
      INSERT INTO items VALUES(@i);
 
      -- Leave the loop
      IF @i > 5 BEGIN
        BREAK;
      END 
 
      -- Increment the loop counter
      SET @i = @i + 2;
    END
  GO

For more information, see Informix to SQL Server Migration.