Decoupling using stored procedures at what cost?

Question

I was taught to decouple code implementation from database implementation through the use of stored procedures. I'm wondering how much true benefit there is sometimes. I know it differs from case to case, so, for instance:

Decoupled DAO Methods (C#)

User FindByName(string value);
User FindByLogin(string value);
User FindByEmail(string value);
User FindByFoo(string value);
User FindByBar(string value);

Contrast that with:

Not-So-Decoupled DAO Methods

User FindBy(string columnName, string value);

This results in significantly less code to be written (though, admittedly, most of it would be cut-and-paste), many fewer stored procedures to be written and maintained, but also couples the code to the database implementation.

Where's the line between good and impractical design practice in this example case?

Answer 1

I find that stored procedures make sense if there is a great deal of business logic involved in order to store data, so, for example, if you are needing to process an order, so you deduct from what is available and then doing some other operations.

If you are just doing some select operations then a function makes more sense, but, depending on what you are doing a prepared statement may make more sense.

Now, if you are not using some data mapping framework, such as DLINQ or Hibernate, then stored procedures and functions may be useful to makea it easier to switch databases while taking advantage of SQL commands that are unique to that particular database.

I do like to keep code as simple as possible, and unless there is a good business reason, then I find prepared statements to be best.

You stored procedures above would be too restrictive, as, if I just want to get name and login, I call two stored procedures? That would be annoying. If there are many tables to be joined, you could create a view and then the SQL query can limit the columns needed, which means that only data that is needed will be returned, which is more efficient.

Answer 2

The decoupling is not as bad as you infer. In your example, you are likely to have FindByName() and FindByLogin(), any further options are not so likely.

What you are actually seeking is a nice separation between your compiled code and the database, not a proliferation of stored procedures. Ideally it should be coded in such a way that it requires little to no change in your compiled code to change not just the content of the stored procs, but the entire database system. Think of it this way - the signature of the stored proc is like an interface, your compiled code doesn't care what happens on the inside as long as the input and output types and rules remain the same.

Of course this is an ideal scenario and it doesn't mean you have to do this, it may be overkill and totally over engineered for your situation. A simple fuzzy FindBy() may be perfect for meeting your current and future requirements. Your application to database call structure can be as granular as you want to make it. But this doesn't mean that your FindBy(string columnName, string value) is necessarily a good option to have because it is tying you to a specific table or view structure (even if you are actually calling a stored proc to implement it), which is what you are trying to keep away from with your code/database separation.

At the end of the day, it comes down to a compromise between:

• does it meet the current requirements
• can it be done within the required timeframe and budgets
• is it easily extensible
• is it maintainable
• is it testable