Grant Fritchey - Thursday, February 8, 2018 4:50 AM

Writing out a full fledged T-SQL for all that would be a full-time position and I ain't getting paid. Ha!

However, I'll offer a few pieces of advice.

First, and most important, get loops and whiles and, as much as possible cases out of your head. Set-based operations is how you need to think in T-SQL. Yeah, you can do pre-loading on sets (meaning put stuff into temp tables or table variables) and then work with those sets, but get away from "for each row do this" thinking. It'll kill you in T-SQL. It's just not good at that. What it is good at is sets.

Next, branches in T-SQL code basically mean, welcome to a new procedure. You don't want branched code inside of a single procedure. It has to do with how T-SQL gets compiled. It compiles the entire batch, all branches, all code, as a set. So compiling while tables are empty or for branch values that could result in one row or one million rows will result in plans that are pretty horrible. When you have a break in logic, we're going down PathB instead of PathA, then that's a new procedure. In fact, create a wrapper procedure that determines paths and then a procedure for each path. Yeah, more work, but you'll thank me after it's done.

Avoid ad hoc code. If you find that you have to build every single query through a series of coded statements, your requirements are off, your database design is off, or the coding is off (ad hoc reporting being the exception here).

Finally, eat the elephant. Go at this one step at a time. Figure out a set-based mechanism to gather your initial data set. Then, determine if you can modify that set for the next step, in the first step. If not, are you looking at "well, for each row..."? If so, stop, reevaluate. There's nothing wrong with, initially, putting it all into temp tables and then putting all that together in a few steps. It doesn't have to be a single, magic query. In fact, sometimes, even if you can do something in one giant uber-statement, it may not perform as well as two or three smaller chunks of code.

Just test the code, the data, and, most importantly, your assumptions as you go.

If you do get to specific steps that are bugging you, post 'em here and you'll get help. Heck, you may get one of our crazed members to take this as a challenge and write the whole thing based on the p-code you provided.