A Case We Shouldn’t Use Stored Procedure’s In

By | June 9, 2009

We are working on this big project at work in which several teams are assigned to different modules. The modules are, naturally, overlapping in certain areas where they they need to interact with each other through API’s.

One of these modules is central and crucial to the rest of the modules, the dependency is very high that the team has to provide many API’s. Certain API’s was needed by different modules; our team needed a list of entity X, and another team also wanted a list of entity X, BUT…we had different criteria!

For example, Entity X had an Enum property called “Type”. The API provided a parameter to filter on this Type, but the options were limited to couple of choices; either you get entities of THIS type, or you get all entities.  If you needed type A and B only, you will have to get all the entities in the database, or make two hits to the database and join the two lists.

A solution was to give all the various options to the user as optional parameters some of which was Array of values. This resulted in an ugly API signature that had many optional parameters, and when ever a new criteria is needed, the signature would change and break all the already existing calls for the API, and I will not even imagine how the SP would look like!. An ugly alternative as well is to create new SP for each different criteria. Both choices are maintenance killers.

In such cases, the dynamic queries are just wonderful; depending on the properties the end user needs to filter on, a query will be created dynamically with proper operator passed. Usually ORM engines, or similar engines, will provide you with an “internal language”, e.g. SubSonic:
Episode  ep = new Select().From<DA.Episode>().Where(“Title”).Like(“SOA”).ExecuteSingle<DA.Episode>();

Another example is the “query by example” in NHibernate (code snippet is taken from NHibernate help):
IList episodes = session.CreateCriteria(typeof(Episode))
    .Add( Expression.Like(“Title”, “SOA%”) )

And, of course, LINQ:

var episodes = from x in db.Episodes where x.Title.Contains("SOA") select c; 

Or you can build your own ;).

I hope this gives an insight.

3 thoughts on “A Case We Shouldn’t Use Stored Procedure’s In

  1. omar qadan

    the most interesting thing with nhibrnate example the you can even create custom class for complex queries with ICriteria

Leave a Reply

Your email address will not be published. Required fields are marked *