Stored Procedures - General or Specific

Question

What is the preferred way to use stored procedures between the following two methods and why:

One general SP such as 'GetOrders' which returns all the columns for the table Order. Several different parts of the application will use the same SP.

OR

Several more specific SPs such as 'GetOrdersForUse1' and 'GetOrdersForUse2' which return a subset of all the columns. Each SP is only used by one part of the application.

In the general case, the application will only use a subset of the columns returned by the SP. I was thinking of using the specific method for performance reasons but is it really going to be worth the extra work? I am developing a web site using ASP.NET and SQL 2005.

Answer 1

Like all great things it depends. How different is the logic in your variations. If for example the only difference is the return columns, then all your saving is some bandwidth over the network and some memory both of which are a lot cheaper then the time its going to take to create the variations test them and maintain them.

Now if there is very significant different selection logic going (joining different tables etc), then you might be better off having specialized SP's.

One last thing don't prematurely optimize. Build it simple and working first, then when you discover you need that extra millisecond then you can look at tweaking.

Answer 2

I would go for your second option simply because you should NOT be extracting data from the database that you don't need (either rows or columns) - it puts unnecessary strain of the DBMS and transmits useless data across the wire, wasting network bandwidth.

Answer 3

Think of them as functions, if you would write a separate function then probably use separate stored procedures. If there is any doubt remaining, use separate stored procedures because:

• it will save bandwith
• it will save memory

I find that separate stored procedures are easier to maintain than one giant one.