One of my favorite ways of using SQL to streamline your code is the utilization of cursors, loops and dynamic SQL. Sometimes I start with this type of solution right out of the gate and sometimes it comes as my code evolves. Today I ran into a data integrity issue in a table and I identified the root cause of the problem was related to code I wrote a while back and the RANK() partition logic was not doing what I originally intended.

I analyzed the code and determined that I really needed to rewrite it to be more efficient. The general description of the issue is I had 12 different columns that needed to be set to a distinct user ID for each parent category. Each sub-category can have different ID’s so the person that has the highest representation within a category gets the default assignment for that row/column.

With 1 column, this is a simple task. However, since I have 12 columns and it could grow later I decided to use a different approach instead of just 12 sets of code repeated for each column. I put together a cursor with the distinct column names in the open cursor statement. While the cursor is in a fetch status, I then write the INSERT and UPDATE statements in a dynamic SQL string that changes while in the loop based on the current column name. The string is then executed each time in the loop which is the equivalent of what I would have wrote if I had just the 1 column. However, now if I ever need to add a new column to the logic all I need to do is add it to the table with the column names. Everything dynamically updates from there.

Although the lines of code was only about 10-15 lines less, now it is cleaner and also fixed the problem that resulted in me working on this task in the first place. Problem solving is not always just about solving the problem.