Instead of working, I blog.

One thing that's as varied as ways to code is how to manage the code itself. Or I guess I should say architect instead of manage, but it all comes down to management.

The 2 major ways are to componentize and to not. And by componentize, I mean taking all the individual components and turning them into small independent chunks that can be called from many sources. A good example is taking a date calculator routine in your SPs and turning it into a function so the SPs just call it instead.
So those are the 2 ways. You either put it inline with the code or you make it into a component and call it from all the modules you need.

Of course, I'm mainly talking about SQL code here, but it really applies to any kind of code I suppose.

It's easy to see the advantages of developing a component solution. This is the DB equivalent of COM, right? But what's not easy to see is what it does for you (or to you) support-wise. What this solution can do is put you in a new form of .dll hell from the old days.

Let's take a simple example...
Let's say that you have sn SP in prod that's giving you problems and you want to track down where the degraded performance is coming from. So you open up the SP and start looking at the code. You see that a major component of it is an external SP call. OK, so you open up that SP. Now you see that that SP calls 3 other SPs. And each one of them is calling more SPs. And so on and so on. This is the definition of spaghetti code isn't it?

I'm actually in the middle of doing this very thing right now. I wanted to stop and blog about it while I was still pissed off. I've been doing this for an hour now and I'm not any closer to a solution than I was when I started. So you can see that while going the COM route can be helpful, you can take it too far. And that's really what happens, isn't it? Devs take a good idea and drive it into the ground until it's so hard to manage it's not even worth having it anymore.

I've been trying to come up with some guidelines for this and it's tough. But roughly, I like to say that if several SPs use the same code, or are calculating the same thing, it's ok to pull out into COM. The same goes with code that gets changed a lot. If you've got code that changes often and is used in a lot of SPs, by all means put it into it's own SP so you only have one place to change it.

Anyway, I guess I'll get back to my plate of spaghetti now.