Anyone who has written stored procedures has more then likely done the following (this is a very trivial, simple example):
WHERE SomeLookupType = 1
What I am trying to show above is selecting some data based on hard coding a reference (lookup) value in the procedure.
I have never really liked doing this for a few different reasons, but just accepted it as the way it was. Here are a few of the reasons I have never really like it.
- Hard codes business related logic in the procedure
I know the procedure is meant to retrieve data by a given value, but if you need to change the value you will have to go to the procedure to do so. I am not a big fan of putting business logic of any type in procedures.
- Hard codes potentially dynamic data values in the proc
If the values in the lookup table need to change for any reason, you will have to remember to go to the various procedures to make the appropriate changes.
- Can make refactoring a little more challenging.
If you are refactoring/changing business logic in your application you will have to remember to visit various procedures to make changes because maybe now you want to lookup the data by an other value.
I really have never put too much thought into how to do it better, but at my current client they have a way that works, but I am still not sure I like it, but it is a step in the right direction.
WHERE SomeLookupType = @SomePassedInValueToTheProc
The logic above is exactly the same, with one twist. The magic (hard coded) value is now being passed into the procedure. This resolves all my issues that I talk about above, but it does bring its own issues and they are.
- Increases the parameter list
I know this may seem like a petty thing, but I like to keep my procedure parameter list to a minimum, and if i have to pass in 'lookup' values it just muddies the water.
- Can cause 'philosophical' dilemma's
Let me explain. I have a procedure that will return data based on its 'status' The status is passed into the proc. However, due to some lame business requirement there is one situation where i need to return the data based on 2 status's. In this case I have 3 options.
1) Change the proc to accept a new additional param.
2) Call the proc 2 times one for each status and combine the results
3) 'Hack' the proc to use 2 values in special cases.
Like I said above, I am not in love with either solution, but I do have to admit that passing in the lookup values appears to be a little cleaner in the long run.
So, is there another way?
Till next time,
01-04-2008 8:26 AM