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.
Remember that guitar in a museum in Tennessee
And the nameplate on the glass brought back twenty melodies
And the scratches on the face
Told of all the times he fell
Singin’ every story he could tell . . .