parameters in a table

  • (Hope this is the appropriate forum.)

    Our team lead wants us to store a value in a table that will be used within a stored proc (versus hard-coding the value within the proc). In this case, it happens to be a number of days that will be used in a calculation. Obviously, if the value is in a table, we can tinker with it while leaving the proc alone.

    Fair enough. Makes sense. However, I can see that this method will likely be extended for other circumstances. Maybe in another case we want a date value or a string, for instance. Having never implemented this, I cannot envision the scenarios and, thus, a reasonably flexible solution evades me.

    We could have a column for numerics, strings and dates but only use the relevant one depending on the case. We could use a single VARCHAR column and convert the value as needed. I am not really comfortable with either of those ideas.

    Any suggestions?

    TIA

  • It seems to me that the best way to do this would be separate tables for separate categories of rules.

    Really, I'd have to know more about the application to say more than that. I definitely don't like the idea of storing the data in nvarchar format and all that, since it violates the basic idea of entities, but it might be the best solution in some circumstances. I can't tell with what little I know of the application you're talking about.

    (I'd have posted the question in either the general discussion or design forums.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I do this fairly often and I typically just have a two field table - one with a unique variable name, and one with a variable value stored as some type of character data (regardless of actual type).

    Since these are variable values typically being set by me, I can be pretty certain data of the wrong type will never be entered so I am not so worried about proper data types. I know it is not good practice, but doing something really complicated seems like overkill.

    If you wanted to be more thorough, you could add a third field that would specify a data type for your variable and use a check constraint to compare the value field to the data type field and verify the type is correct. Unless you are planning on these fields to be updated by users through some GUI, I don't think it is all that necessary.

  • Thanks to you both.

  • I agree with Michael Earl... I've done this many, many times (cofiguration table that looks like a name/value or "EAV" table). I've found that it's very handy. When you load the values from the table into variables, you need to ensure that you do the conversion either implicitly or explicitly then and there. That will keep other implicit conversions from doing things like possibly causing index scans instead of index seeks and the like.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply