Stored Procedures - General or Specific

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.

From stackoverflow

• 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.

  paxdiablo : Bandwidth/memory are VERY important IMO. SPs are written/tested once (or rarely), run many times. Hence the cost of writing/testing tends towards zero. Your point about optimization is good - I would not use SPs until you've locked down all queries, then shift your SQL across to the back end.

  JoshBerke : They are important when you get into the optimization phase. And while one or two SP's might not be bad trust me when you get to the 8,000 SP in a single DB you know something is wrong (Especially when you start finding dupes cause you have so many)

  paxdiablo : I have the advantage of working with mainframe DB2 - all SQL queries and stored procs are vetted by the great DB2 DBA gods and they will optimize them (including combining dupes). You're right however, 8000 is horrific!

• 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.

• 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.