Derik Whittaker

Syndication

News


Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Using lookup values in Stored Procedures

Anyone who has written stored procedures has more then likely done the following (this is a very trivial, simple example):

Select Column1,
           Column2,
           Column2,
           etc...
FROM Table1
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.

Select Column1,
           Column2,
           Column2,
           etc...
FROM Table1
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,


Posted 01-04-2008 8:26 AM by Derik Whittaker
Filed under:

[Advertisement]

Comments

Michael O wrote re: Using lookup values in Stored Procedures
on 01-04-2008 10:43 AM

With Oracle, your stored procedure's lookup dependency could be against a package variable.  You could initialize that package variable with some default value and modify it as needed.  Your would then have both the shorter signature and flexibility you desire.

<pre>

create or replace package MyPackage is

  MyPackageVariable number;

  procedure MyProcedure

  (

     param1 number

    ,param2 number

  );

end MyPackage;

create or replace package body MyPackage is

  procedure MyProcedure

  (

     param1 number

    ,param2 number

  ) is

     MyProcedureVariable MyTable.MyColumn%TYPE;

  begin

     null;

           select MyColumn

             into MyProcedureVariable

             from MyTable

            where MyTable.LookupColumn = MyPackageVariable;

  end;

begin

  -- Package Initialization

  MyPackageVariable := 0;

end MyPackage;

</pre>

David wrote re: Using lookup values in Stored Procedures
on 01-04-2008 10:56 AM

Pass in XML (@xml text) and parse it in your procedure using Open XML

DevKm wrote re: Using lookup values in Stored Procedures
on 01-05-2008 10:41 PM

Couple of options I could think of. Xml text and Open Xml is certainly one of them.

Another option that I have used quite often is sending the coma seperated list of values , building a temporary table by parsing the text. There is a price to be paid by way of reduced performance ofcourse,  

Kalpesh wrote re: Using lookup values in Stored Procedures
on 01-06-2008 4:46 AM

Have separate stored procedure for different purpose

for e.g. GetAllOrdersWhichAreDelivered (status = 5)

GetAllOrdersWhichArePending (status = -1)

In this case, the caller need not know - what each of the status value means.

Hani wrote re: Using lookup values in Stored Procedures
on 01-06-2008 11:53 PM

You can pass a comma separated value which contains a single ID or multiple IDs.

Select Column1,

          Column2,

          Column2,

          etc...

FROM Table1

WHERE SomeLookupType in (SELECT BigIntValue FROM CsvToBigInt(@SomePassedInValueToTheProc)

CREATE FUNCTION [dbo].[CsvToBigInt] ( @Array varchar(3999) )

returns @BigIntTable table (BigIntValue bigint)

AS

begin

 declare @separator char(1)

 set @separator = ','

 declare @separator_position int

 declare @array_value varchar(4000)

 set @array = @array + ','  

 while patindex('%,%' , @array) <> 0

 begin

   select @separator_position =  patindex('%,%' , @array)

   select @array_value = left(@array, @separator_position - 1)

   Insert @BigIntTable Values (Cast(@array_value as bigint))

   select @array = stuff(@array, 1, @separator_position, '')

 end

 return

end

Dave Schinkel wrote re: Using lookup values in Stored Procedures
on 01-12-2008 2:52 PM

>>>Have separate stored procedure for different purpose

Sure, you're gonna have this naturally.  But this can be  bad instead of just using params.  Why?  Do you want 10,000 procs in your database?  This is a management nightmare.  This is way inefficient to think that you should just create  a new proc for every type of transaction out there.

While people debate dynamic SQL, it is no longer as much as a security risk these days with SQL 2005, and if you developt your n-layers correctly, you can protect against the worries of dynamic sql pretty well.

Therefore, use Code Generated Templates and stored procs that are dynamic in nature that are bubbled up then to your templates.   This is good because:

1) Reduce the # of stored procs in your enviornment as well as repetative procs that essentially could be doing the same thing.  A lot of developers are lazy and most of us who are not lazy, don't also want to go through 10,000 procs to find out if there is an "existing" proc in the system that does what we're looking for.  If you have a controllable set of procs for different tasks, any variations that you need can be handled then through the DAL, not at the proc level...so you can reduce the # of required procs (different procs) at the DAL level by trying to reuse what procs were created by the template as much as possible rather than just hurry to just create another proc in the database.

2) You're able to control exceptions in the OO layer rather than fixing your nightmarish proc issues in the DB layer for  many reasons

3) The procs can be generated by the code generation tool.  This has tons of benefits such as consistent proc code, management in # of procs needed for templates, and overall more efficient database then and OOP layers.

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)