Excellent article on cursors, Wagner.

Here is a weird problem that made me choose a cursor. The incoming data was in a header / detail format. The header had 5 columns: ICN Code, Officer, Department, Function, and Message. Essentially, it is a Money Transfer by Wire application running on a Tandem system. The data provisioning was in pure TAB Delimited Text, so I used OPENROWSET and a format file to load the daily files of 50,000 rows.

But the rows were really weird. The header had data in 4 out of five columns, and the detail had data in one out of five columns. The rows between headers were the details for the prior header. Where the last column (Message) was NULL on the header marked the end of the last header - the Message being NULL was a sign that the it was a header row. Where the Message was not NULL there were all kinds of "field codes", where each field code has very odd and specific flags and options. Each row of the message had space for 2000 VARCHARS. Row counts varied between headers too - not all the headers had the same number of Message rows.

The problem was to pick up the Currency, Amount, Customer ID, and Originating institution from each of the Message Rows for each of the headers. I used a cursor, much to my dismay. It is slow, but it works and gives me fine-grained control.

I would like to try a set based approach too. If you pick up this comment, let me know, and I can send you more details.

Bob