We just started migrating from 2008 to 2014 this past summer and we're still working on getting everyone moved over. I can't imagine how well we'd do if we had...

I'd be happy...

We're in the middle of a huge project that's migrating an old legacy database into the current model, and we're using this strategy heavily, along with instead of triggers on...

I'm particularly curious about the use of the date escape sequence (e.g. {d '2012-11-15'}).

Is it good practice to use those if you're having to hard code dates in procedures, etc.?

Ah good, gives another small bit of ammunition to maybe upgrade to 2012. 🙂

The actual behavior isn't matching the ON SET NULL either, unfortunately. I looked a little more closely...

Well, while...

opc.three (4/9/2012)

---

This is a very small amount of info to go on....but I'll take a guess. It looks like your inner proc is not raising an exception whenever it rolls...

I found it interesting that the execution plan for the union in the question was the same as if I had done a distinct from the union all

select distinct a...

I'm often amazed at what our users manage to figure out how to do (or more often, don't remember how they managed to do it).

We run into the trouble where...

Well, I wound up doing some unit testing yesterday with both the XML version I'd written and a slightly altered version of yours (really the only change that I wound...

Wow, thank you, Jeff. I'd been trying to think how to use your Tally example, but hadn't quite wrapped my head around how to do it in a case...

Thank you for the help; I look forward to seeing it.

I decided to see what would happen if I converted the concept in the original proc to be XML. ...

Yes, the Note_ID is just the @NoteID value. According to the notes I have, it's because of how the report uses it as a parameter, so it needs to...

That temp table at the end of the original code is actually the only table it gets inserted into. The proc is used by an SSRS report, the results...

The attached text file is the text of the long example I've been using that I think should give you a better idea of the overall formatting.