I once worked on a project to convert a discontinued enterprise resource planning (ERP) system into a Web-based application because the original system would not work on our Windows XP desktop machines. The original, off-the-shelf system was designed to be customized to fit each client. For the Web-based version, our team wanted to convert the hard-coded business logic in the front end into stored procedures and functions, with the presentation layer coded in ColdFusion. We wanted to accomplish this while preserving bandwidth and hitting the server as little as possible. But as with many projects, we ran into trouble.
The recurring problem
One of the biggest problems we encountered involved handling list data. We would have arrays of check boxes, lists of information, or many line items that we had to process using ColdFusion to isolate each item and then process it individually by executing a stored procedure repeatedly. This meant that a list of 10 items required 10 ODBC connections to be established between the ColdFusion server and the database, along with10 executions of a stored procedure to handle each item. This compromised the integrity of our transactions. If one item failed, we would need to recognize the failure and call another piece of code to handle it, since we essentially had 10 individual transactions to roll back. Coding the list-processing features on the server meant that the code wasn't easy to reuse for several similar areas. One implementation of handling list data in ColdFusion would be to loop over the list myList using cfloop to call the stored procedure myStoredProc for each element, as shown in Listing A.
Because of the shortcomings of that approach, I decided to find a solution that would:
- · Minimize hits to the database.
- · Maintain transaction integrity over a batch of updates or inserts.
- · Be reusable.
I considered various approaches, one of which would be to strip out lists of items on the Web application server and process each list element as a single item. To do this, we'd need to build SQL stored procedures to handle individual items and create a new list handling routine for each new interface connecting to the SQL back end. But this would introduce the potential for transaction failure and would require nonstandard rollback code to be written.
The second idea was to build dynamic SQL WHERE clauses on the Web application server and pass them into a stored procedure. First, we'd preprocess the list to wrap each item with single quotes: 'adam','bob,'rita'. Next, we'd pass the list as the parameter @mylist into the stored procedure and set the WHERE clause to find items in (@mylist), using sp_executesql to produce the results. This would work only for SELECT and UPDATE statements.
Finally, since SQL Server 2000 introduced user-defined table valued functions, we could create one that, when passed a string, would use a WHILE loop with the PATINDEX function to find each element and add the row to the return table, along with a sequence identifier to indicate the position of the element in the string.
I decided that a third option involving user-defined functions would work best for us because it was a reusable solution that would reduce the amount of work done by the Web application server. It also would extend the capabilities of the list through the introduction of a sequence ID and the ability to sort the table, return the number of elements, and more. (See the section of extensions.)
The code in Listing B creates the SQL Server 2000 user-defined function fn_ListToTable, which, when passed a list, will return a table type with two columns:
- · seqid—The sequence ID identifies the position of an entry in the list, since some lists carry important information in their order.
- · entry—The entry is the individual list element being passed. For my purposes, the list would never be longer than 8,000 characters, and no list element would exceed 8,000 characters.
You could use the list to cursor over the resulting table to insert the data or use it to replace an in clause with a join on this table. For example, the call
would return a table of three values, where Adam has a sequence ID of 1, Bill of 2, and Roger of 3.
You can employ more list functions based on this simple function to process lists. One of the most crucial elements of handling lists is being able to locate items. Similarly, knowing how many items you have and being able to change their order can be vital. The list modification functions below will probably be the least useful, since any modifications to the data will typically be applied against existing tables and rows, but they do fill out the list processing toolbox.
Changing the list order
order by entry
- · ListSort(@list,@direction) returns the sorted list as a list.
- · If your list is structured as ordered data, you can use the modulus of the sequence ID to determine columns of data to insert. For example, SELECT * FROM fn_ListToTable('Adam,28,Bob,32') WHERE mod(seqid,2)=0 would give you the values "Adam" and "Bob," which are both names, whereas mod(seqid)=1 would give you the second column of data, as shown in the snippet below:
Searching for items
- · ListLike(@list,@matchstring) returns a table of all list members, such as '%string%'.
- · ListContains(@list,@item) returns true or false based on the presence of the item in the list.
- · ListFind(@list,@item[,@all]) returns the first seqid of the item in the list unless the parameter @all is provided, in which case it returns a list of all the sequence IDs of the matching entries.
- · ListGetAt(@list,@pos) returns the element at a specified position.
Selecting list elements
- · ListRemoveDupes(@list) returns a list of distinct values.
- · ListFirst(@list) returns the first element in a list.
- · ListLast(@list) returns the last element in a list.
- · ListRest(@list) returns everything but the first element in a list.
Counting the number of elements
- · ListLen(@list) returns the number of items in the list.
- · ListValueCount(@list) returns a count of the number of unique items in the list.
Modifying the list
- · ListInsertAt(@list,@element,@pos) returns a list with the new element inserted at the specified position.
- · ListSetAt(@list,@value,@pos) returns a list with the element at the specified position set to the specified value.
- · ColumnToList(@tablename,@column) reverses the operation to pass a list of items out of the database as a string instead of a recordset.
Reduced connections yield a good outcome
The function allows us to pass lists of data into functions, so we can wrap any update statements in transactions to ensure that each item is processed or none are. Any interface handling the presentation layer now uses the same logic for handling lists, which minimizes the potential for interface-related discrepancies in the code. Most importantly, we have reduced the number of connections created between our Web application server and the database to a single call, reducing traffic and improving application speed.