Delimited List Processing in SQL

by Michael Sansoterra

[The code for this article is available for download.]

I recently worked on the database side of a Web project for which the Web developers needed to pass several item numbers to an iSeries for processing. Traditional methods would have dictated these items be inserted into a temporary or transaction file, then converted and processed as an XML file or passed individually to a stored procedure for processing.

An alternative that is fast and painless (provided the related item information is minimal) is to make a single call to a stored procedure and to pass the item numbers in a delimited list as a single parameter (for instance, "ITEM1,ITEM2,ITEM3"). This approach is great in terms of performance but is awkward to process in SQL.

You can remove the awkwardness by creating a user-defined table function to transform the delimited list into a set of rows. For the record, a table function (available in V5R2) is a program designed to supply data to SQL in a tabular format. Consider this table function:

Create Function xxxxx/ListUDTF
             (parmList     VarChar(24576),
              parmDelim    VarChar(10),
              parmMaxItems Integer)
Returns Table(ItemNo Integer,
              Item VarChar(128))
External Name 'xxxxx/LISTUDTFR(PROCESSLIST)'
Language RPGLE
Disallow Parallel
No SQL
Parameter Style DB2SQL
Deterministic

The function accepts three parameters: a delimited list, a delimiter, and a maximum number of items to return. The RPG program LISTUDTFR transforms the list into rows such that the following statement:

Select *
  From Table(ListUDTF('ITEM1,ITEM2,ITEM3',',',9999))
       As List

Returns the following table:

The function can then be used in a stored procedure to create multiple order lines based on a delimited list of items and order quantities, such that the Web developers only call the procedure once:

Create Procedure spAddWebOrder
(parmOrderID Integer,
 parmItems   VarChar(1600),
 parmQtys    VarChar(1600))
Language SQL
Modifies SQL Data
Set Option Commit=*None
Begin
    Insert Into OrderLines
    Select parmOrderID, Items.Item, 
           Cast(Qtys.Item As Integer) As Qty,
           Part.ListPrice
      From Table(ListUDTF(parmItems,',',9999)) 
           As Items
      Join Table(ListUDTF(parmQtys,',',9999))
           As Qtys On Qtys.ItemNo=Items.ItemNo
      Join PartMaster
           As Part On Part.PartNo=Items.Item
End

As you can see, the possibilities for delimited list processing are enormous. To tinker with this table function yourself, download and compile RPG program ListUDTFR. Follow the instructions in the header for registering the function (using the CREATE FUNCTION statement).

In terms of programming, an external table function (written in a high-level language) is similar to an external scalar function, except that it can return multiple column values and multiple rows.

When invoked in an SQL statement, SQL will call the function's program with a pre-defined call type (OPEN, FETCH, CLOSE.) When a fetch is requested, the program is expected to fill in the output parameters to build one row of data and exit. This process is repeated until the program sets the SQLState parameter to 02000, which indicates that it has no more data to return.

Further, the table function is given opportunity to do initialization and cleanup work (during the OPEN and CLOSE calls), which occur once at the beginning and end of each function invocation. During the open, any initialization work should be performed. During the close, all cleanup work should be performed and, in the case of an RPG program, *INLR should be set on to end the program completely.

For help understanding how to build SQL function parameter lists, see the article "Scribble on SQL's Scratchpad." For more information on creating table functions, see the SQL Reference (in PDF format) and SQL Programming Concepts (also in PDF format) manuals.

Michael Sansoterra is a programmer/analyst for SilverLake Resources, an IT services firm based in Grand Rapids, Michigan. E-mail: msansoterra@silver-lake.com