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
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.