Its funny, I came here looking for what I thought would be exactly this article, but it didn't end up helping me at all.
What I wanted was more of a best practices article I think -- With suggestions about error checking, commands that shouldn't be used in triggers or stored procedures, commenting code, naming conventions, performance hints, etc.
I think probably error checking would be good for an article in and of itself. Do you have special error handling stored procedures? Do they email the dba? Do they raise errors? Record errors into an error table?
I wish I did more with stored procedures in general, I'd give writing such an article a shot... Maybe I should post an idea like that to the forums, let you all rip it up until I get it right, and then we'd post it together?
Or maybe my google-foo just isn't good enough and such an article on error handling already exists?