Thanks all for the feedback! Keep it coming, please.
First: Bstauffer, we all have rough days. I'm glad you brought up the point you did because I learned from Adam's and Icocks' responses. William's response was closer to why I did it that way - I like to "manage" the test conditions, and in this case "managing" them means writing a condition in the IF EXISTS statement that I can highlight and execute in an SSMS query window and view the results I want in a format I prefer. I'm by no means asserting this is right or even good (and I wouldn't dare call it a "best practice"). I simply say it works for me now. I'm still learning - I learned something today about IF EXISTS - how cool!
I'm really impressed by the fact you added another comment. That was very cool of you - thanks!
William, thanks for the comment about ALTERing procedures and the defense. As I said, your reason was closer to my own.
Adam and Icocks, thanks for schooling me! I probably would have offered the defense that the script should run a minimal amount anyway, and shouldn't be performance-tested; but it would have bugged me that I wrote something that may not perform well - even if it only costs a few milliseconds per year. I'm not making that up: I am an engineer. It would have bugged me.
Agilist, you've given me homework. I want to know more about DbFit! I am with you on CI for database development (and actually for BI development as well). I've been preaching this since the PASS Summit 2007 and, in my opinion; Microsoft is starting to give us the building blocks we need - integrated, even. One step at a time, right? We'll get there.
My only defense for beginning this series where I did is: I want to start at the footing of the foundation for where I'm going, which is Continuous Database Integration (CDI anyone?). Yes, I am digging some. Yes, it's hard work. But I am attempting to cover fundamentals in their primitive form.
JJ B, thanks for your kind words. You and William make a strong case for ALTER vs. DROP / CREATE for procedures. I don't disagree with your approach. I hate recreating permissions - I always forget one! And that completely slipped my mind. I'll have to incorporate it into a future article in the series and give you both credit for pointing that out.
I like to write "buffet" articles. There's some stuff here you want to try, give it a try. It's safe - it's just a little article and snippet of demo script you can whip up in a jiffy. If it looks really awful and you don't have the stomach for some piece, just move along... maybe the next thing will be more appetizing. That's my philosophy.
I'm working on article 3 in the series. Sneak peek: The two main points are refactoring and auto-generating some of these tests.
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics