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