SQL Server - Many queries or one long stored procedure?

I know, "it depends".  But all other thing being equal, what's a "best practice"?  Coming from the world of Access it's not uncommon to build up a stack of queries for a single business process.  I can do that in SQL Server, too, but should eliminating the sheer number of procedures be a priority at all?  Or is it best to have numerous, step-wise procedures on the premise that it is easier for the next guy to follow?

You are right, it really does depend.  If it is a single long business process, I'd consider multiple queries inside a single stored procedure for the sole purpose of not letting changes to another stored procedure trigger problems in your business process.  Those change areas present significant change management and support risks going forward.  Otherwise, each time you deploy changes to any of those dependent queries, you have to also test all processes that use your long business logic procedure.

The "don't duplicate code" academic thinking amongst developers and dba's is a good thing.  But, there are also many occasions where duplication of code is the better option for application stability and application evolution (the bigger risk).  I always choose the latter because ultimately it is better the application work with substandard code/design than an application break because the code would get better code reviews from a book author.

Thx Robbe.  Agreed...it's about getting the job done, after all.