My first foray into dynamic SQL was in 2006 at Bank of America. After completing a couple of projects, I started thinking about the similarities between them. So on the next project I built a lookup table to produce the reporting output (we were using Excel pivot tables for reporting).

Given that performance was a concern, it was better to pivot the data in the database instead of having Excel do it. I went that same route for the remainder of my projects there, because it made for a much smoother development process that was fairly easy to maintain.

Another instance of taking this lookup approach is when I eliminated a view that was far too slow at Wells Fargo. My thinking from the start was that I didn’t see the point of combining all of our 300+ column archived tables. My approach was to provide easy access to “everything” but do it with flexibility.

The reality is that they would never need more than 10% of those columns at one time for this reporting purpose, so it seemed excessive to union everything together in a view.

Nevertheless, I did what I was asked to do with the view, but in less than an hour I worked up the dynamic SQL approach as well. The former didn’t work at all and the latter returned results in seconds. I had a bit indicator set up to flag the baseline of columns needed, and anytime the users wanted anything else, I just updated the flag and kicked off the procedure.

More recently at International Game Technology I rigged up some dynamic SQL to rename all of our constraints to a new naming convention. There were some other maintenance tasks along those lines — as well as other uses I’ve implemented over the years.