R.P.Rozema (11/2/2010)

---

Good article, thank you!

One addition though: if your procedure is called from within a trigger or from a service broker handler, most likely the catch block won't be called upon many errors. Instead execution is resumed from the 1st line after the call to your procedure upon these errors. This is because in these contexts the xact_abort setting defaults to "on". To make sure your catch block does get executed for most (but still not all) errors, include a "set xact_abort off;" line as one of the first statements inside your procedure, before you open your try block. Adding this line won't harm your procedure in other contexts, but it makes sure your catch block will get executed even when called from these contexts.

vishal.gamji (11/2/2010)

---

What are your views on using this kind of logic in an ETL flow for slowly changing dimensions - overkill?. What about the performance implications when it goes into the slow path?

For OLTP applications, this looks like an AWESOME post. Can someone comment on ETL usage?