Life Hacks to Save Our Sanity

Jess Pomfret (b|t) asked us in this month’s t-SQL Tuesday topic to talk about a life hack we’ve done that’s made things easier for us. This is perfect timing for me; I wanted to share a couple of stored procedures I wrote for the book (Refactoring Legacy T-SQL For Improved Performance). The CodeCascade procs can be found on a GitHub Repo called CodeCascade.

Actually, the idea for this code started back when I listened to Andy Yun (b|t) give a talk about nested views. I was dealing with a 2,000 line stored procedure that would be interesting to rewrite, but it really shouldn’t take me that long, right? I mean, 2,000 lines. Except that it called a function that was also 2,000 lines, which called a few procs, not to mention a few other things the original proc called, and then … well, yeah, you get the point. 20,000 lines of code later, I was like, WHAT did I get myself into?

So, since we all know how excellent we are *cough* at estimating time to perform tasks, I figured it would have been a good thing to have known about this cascading miasma of code before I dove in head-first. Andy’s presentation had code to show the cascade of a view – all of the views it called, and views they might call. I wanted to adapt this to work for stored procedures and functions as well. (Forewarned is forearmed, after all.)

So, I blithely knocked together some code to tell me exactly what objects (procs, views and functions) that my code called, using SQL Server’s dependency information. I went to run it on that exceptionally offensive proc. And guess what? It blew out after 200 recursions. So, I set the limit to 400 recursions, and… it blew that out as well. OH NOES! Well, I had also set a kind of breadcrumb trail field in this because I was particular about the sorting (I wanted each cascaded item to be under the parent that was calling it.) So, I had each object line check to see if that ID was already in the breadcrumb field, and if it was, add a message that it was part of a loop, and stop trying to recurse through that particular line.

Once that was polished up, I also did the “reverse” – what code CALLS my code, and what code calls THAT code? This is again based on dependencies, and checks for that eternal loop.

These procs have come in handy many times. I encourage you to use them and modify them if you’d like, but it’d be great if you left the intro flowerbox on that attributes the original code to me. If you do make changes that you think others would find helpful, please feel free to let me know and I’d be happy to add your changes and attribute them to you as well!

I hope these can help you as well 🙂