FOREACH Statement - Query Loop - Informix to SQL Server Migration

In Informix, you can use the FOREACH statement to execute a query and loop over returned rows. In SQL Server, you can open a cursor for the query, and fetch all rows using the WHILE statement.

Informix:

  -- Sample procedure
  CREATE PROCEDURE foreach1()
    -- Declare and initialize local variables
    DEFINE cnt INTEGER;
    DEFINE items INTEGER;
    LET cnt = 0;
    LET items = 0;
 
    -- Execute query and process all rows
    FOREACH SELECT c_items INTO items FROM items
      LET cnt = cnt + items;
    END FOREACH;
 
  END PROCEDURE;

SQL Server:

  -- Sample procedure
  CREATE PROCEDURE foreach1 
  AS 
    -- Declare and initialize local variables
    DECLARE @cnt INTEGER;
    DECLARE @items INTEGER;
    SET @cnt = 0;
    SET @items = 0;
 
    -- Execute query and process all rows
    DECLARE cur CURSOR FOR SELECT c_items FROM items;
    OPEN cur;
    FETCH cur INTO @items;
 
    WHILE @@FETCH_STATUS = 0 
    BEGIN
      SET @cnt = @cnt + @items;
      FETCH cur INTO @items;
    END
 
    CLOSE cur;
    DEALLOCATE cur;
   GO
 

Specifying Cursor Name

The FOREACH statement allows you optionally to define the cursor name so you can specify query options like WITH HOLD, and use the cursor in statements like UPDATE WHERE CURRENT OF in the loop body.

Informix:

  -- Sample procedure
  CREATE PROCEDURE foreach2()
    -- Declare and initialize local variables
    DEFINE cnt INTEGER;
    DEFINE items INTEGER;
    LET cnt = 0;
    LET items = 0;
 
    -- Define cursor name, execute query and process all rows
    FOREACH cur FOR SELECT c_items INTO items FROM items
      LET cnt = cnt + items;
    END FOREACH;
 
  END PROCEDURE;

SQL Server:

  CREATE PROCEDURE foreach2 
  AS 
    -- Declare and initialize local variables
    DECLARE @cnt INTEGER;
    DECLARE @items INTEGER;
    SET @cnt = 0;
    SET @items = 0;
 
    -- Define cursor, execute query and process all rows
    DECLARE cur CURSOR FOR SELECT c_items FROM items;
    OPEN cur;
    FETCH cur INTO @items;
 
    WHILE @@FETCH_STATUS = 0 
    BEGIN
      SET @cnt = @cnt + @items;
      FETCH cur INTO @items;
    END
 
    CLOSE cur;
    DEALLOCATE cur; 
  GO

For more information, see Informix to SQL Server Migration.