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:
Red | FF0000 |
Blue | 0000A0 |
White | FFFFFF |
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:
Red | FF0000 |
Blue | 0000A0 |
White | FFFFFF |
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.
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.