Should stored procedures be many or focused?

Question

How generic should you make your stored procedures? Should they be considered "one hit" or mirror more general use? Consider the following cases:

1.

• FindUser(username, ...)

  Stored procedure returns the user id to the caller

• FindItem(itemname, ...)

  Store procedure reurns the item id to the caller

• AddOrder(userid, itemid, ...)

  The returned ids can then be passed to the third stored procedure

2.

• AddOrder(username, itemname, ...)

Here, the lookup for ids is done inside the stored procedure

Is there a preferred/recommended way? Thank you for your thoughts.

Answer 1

Personally I'd go for multiple oneHit SPs, since creating a chunky one adds a lot of complexity to the error handling. Invalid itemname but valid username etc. ... On the other hand one could argue that from a performance point of view the chunky ones might be better.

I'd go for multiple ones, but I'm interessted too what other ideas are around...

+1 for the question.

K

Answer 2

I believe that stored procedures should be used for data intensive business logic, where the calculations might be too performance intensive in the application.

Having a complex sp, puts the calculations at the database layer, avoiding massive dataset transfers to the application. This will also allow the programmer to change functionality without having to recompile, but can slow down debugging.

I found that using sps to merely retrieve data for an object (eg user) by key (eg userid) or a specific field is mostly found using auto generated data access layers, and we have found that a lot of the times, there are MANY sps generated per object.

We try to limit sps to report dataset and complex data calculations/manipulations.

Answer 3

Don't fall into the trap of thinking that having to manage/maintain many simple procs is more difficult then just a few complex procs. I've always found that whenever we try to make one procedure do multiple things as opposed to one focused thing, it becomes so much harder to debug and test it.

You say "should stored procs be many or focused?", I would suggest that this is an incorrect comparison.

If you have many stored procs, it follows that they will be more focused because they will be smaller and do less. Perhaps you really mean to ask "Many focused stored procs, or fewer broader stored procs?"

A tool such as db ghost and others should also be used to source control your stored procedure code (and schema) so you treat it just like any other code. With this in place, management of many smaller procedures is even easier.

This is really just applying the core good design practice of separation of concerns to your "database code" as well as all other code.

As another answer also says, I'm a fan of using stored procs to "crunch" your data, as close as possible to the database, such as in reporting procs. This is due to performance reasons, ie getting the db to do what it is good at.

However when it comes to a lot of other type of "business logic", we generally try to keep this out of stored procs and in normal code where we have much better debugging and testing facilities.