A SQL Programming Nugget: SQLSTATE and WHILE loops

Last week I taught an introduction to DB2 application development course at a large financial institution. That gave me the opportunity to share with the students some of my own experiences in the area of SQL programming. I'll share one of these "school of hard knocks" experiences with you via this blog entry, and I'll describe another in a subsequent post within the next few days. The situations I'll cover in these two entries (this one and the one to follow) occurred in a DB2 for Linux/UNIX/Windows (LUW) environment, but I believe that the lessons learned apply equally to DB2 for z/OS systems.

On, now, to SQL programming experience number one.

I was working on the development of a SQL stored procedure that would process a query result set generated by another stored procedure (a SQL stored procedure, by the way, is a stored procedure written using SQL procedure language, aka SQL PL - I've blogged a number of times on this topic, including an overview treatment posted last year). In other words, I was writing stored procedure A, which was to call stored procedure B. Stored procedure B, defined with DYNAMIC RESULT SETS 1 (indicating generation of a result set that would be consumed by a calling program), declared a cursor WITH RETURN TO CALLER (also necessary to make the query result set available to the calling program), opened that cursor, and returned control to stored procedure A. In stored procedure A I declared a result set locator variable, used an ASSOCIATE RESULT SET LOCATOR statement to get the location in memory of the result set generated by stored procedure B and assign it to the aforementioned result set locator variable, and allocated a cursor to enable the fetching of rows from the result set. So far, so good.

I decided to use a WHILE loop in stored procedure A to retrieve and act on each row in the result set generated by stored procedure B. Wanting to exit upon reaching the end of the result set (i.e., after FETCHing the last row), I started the loop with WHILE SQLSTATE = '00000' DO (having declared SQLSTATE - a return code value set by DB2 following execution of an SQL statement - in my SQL procedure and initialized it to '00000'), thinking that SQLSTATE would go to '02000' upon execution of the first FETCH statement following retrieval of the last row in the result set. Indeed, SQLSTATE is set to '02000' in that situation, but I messed up. How? By not making FETCH the last statement in the WHILE LOOP. See, I followed the FETCH in my loop with a SET statement that concatenated the value of a variable with a character string. That statement was always successful (indicated by SQLSTATE = '00000'), so, SQLSTATE-wise, here's what happened when the WHILE LOOP was executed following the retrieval of the last row in the result set:

FETCH cursor-name INTO variable1, variable2, ...; --SQLSTATE goes to '02000'
SET variable_xyz = variable_xyz CONCAT variable1; --SQLSTATE goes back to '00000'
END WHILE;

Thus, SQLSTATE was always '00000' when the end of my WHILE loop was reached, and the loop-evaluation condition of SQLSTATE = '00000' (the condition for initial entry into the loop and for loop reiteration) was always evaluated as "true". The result was an endless loop. Fortunately, I ran this SQL procedure on an instance of DB2 on my laptop, so I was the only person impacted. I got rid of the looping procedure, corrected the logic error, and went on from there, having learned a good lesson.

So, if you write a SQL stored procedure that processes a result set generated by another stored procedure, here are a couple of things to consider:

• A WHILE loop is fine for FETCHing through that result set, and you can even make SQLSTATE = '00000' the loop-evaluation condition - just MAKE SURE that FETCH is the last statement in the WHILE LOOP (before END WHILE, that is), if your intention in coding WHILE SQLSTATE = '00000' DO was to have DB2 exit the loop upon execution of the first FETCH following retrieval of the last row in the result set.
• Alternatively, consider indirectly using the SQLSTATE value to establish an exit-the-loop condition. This could be done by coding WHILE exit_ind = 0 DO (with exit_ind being a variable declared in your SQL procedure and initially set to 0), and then following the FETCH in the loop with IF SQLSTATE = '02000' THEN SET exit_ind = 1 (or IF SQLSTATE <> '00000' if you want to be more generic).

If, in your SQL stored procedure, you will be processing a result set generated by a SELECT statement in your procedure (as opposed to a result set generated by a cursor declared and opened in a stored procedure called by your stored procedure), consider using the FOR statement instead of a WHILE loop. A nice thing about FOR is that you will automatically exit the FOR loop after you've retrieved all the rows in the result set. Note that you could use FOR to process a result set generated by a stored procedure called by your stored procedure (let's call the lower-level program stored procedure B), if stored procedure B inserts the result set rows into a temporary table that is subsequently referenced in the SELECT statement in the FOR loop coded in your stored procedure.

I hope that your organization is using SQL stored procedures to access DB2 data - I'm a big fan of this approach. Check back in a few days for another SQL programming nugget - this one involving user-defined functions.