RETURN WITH RESUME - Informix to SQL Server Migration

In Informix you can create a stored procedure that returns multiple rows using RETURN WITH RESUME statement.

This allows you to execute the procedure in the FROM clause of a SELECT statement. When you convert such procedures to Microsoft SQL Server you can use a table-valued function.

Let's create a simple example to demonstrate how to return multiple rows from an Informix procedure:

Informix:

  -- Iterator procedure returning 3 rows to caller
  CREATE PROCEDURE colors_list()
      RETURNING VARCHAR(30), CHAR(6)
 
     -- Return rows
     RETURN 'Red', 'FF0000' WITH RESUME;
     RETURN 'Blue', '0000A0' WITH RESUME;
     RETURN 'White', 'FFFFFF' WITH RESUME;
  END PROCEDURE;

Now you can execute this procedure in SELECT statement as follows:

Informix:

  -- Calling iterator procedure in FROM clause
  SELECT * FROM TABLE(colors_list());

Result:

In SQL Server you can use a table-valued function to return multiple rows to the caller:

SQL Server:

  -- Table-valued function returning 3 rows to caller
  CREATE FUNCTION colors_list()
      RETURNS @colors_list_tab TABLE (c1 VARCHAR(30), c2 CHAR(6)) AS
  BEGIN 
     -- Return rows
     INSERT INTO @colors_list_tab VALUES ('Red', 'FF0000');
     INSERT INTO @colors_list_tab VALUES ('Blue', '0000A0');
     INSERT INTO @colors_list_tab VALUES ('White', 'FFFFFF');
     RETURN;
  END
  GO

Now you can invoke this function in SELECT statement as follows:

SQL Server:

  -- Calling table-valued function in FROM clause
  SELECT * FROM colors_list();

Result:

Note that TABLE keyword is not used in SQL Server. Also unlike scalar user-defined function, you can invoke a table-valued function without schema qualifier - dbo.colors_list() i.e.

• Informix to SQL Server Migration Tools and Reference
• Online SQL Conversion Tool

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - October 2013.