Variable Default Values to a Stored Procedure (SQL Spackle)

  • dwain.c (9/3/2013)

    Jeff Moden (9/3/2013)

    venoym (9/3/2013)

    In order to bridge the gap between Mr. Hugo's excellent comments and other comments about multiple columns, I've seen systems that used a type specific column in their normalized settings table to handle for strongly typing the parameters (or settings). That way a 2 is typed as Int (or BigInt, etc) and not as a string to be inlined into a dynamic query setting.

    I'd use SQL_Variant for such a thing. It's still "loosly" typed based on whatever data you put in but it will be treated as whatever type you put in. You can even interogate the SQL_Variant column with the SQL_VARIANT_PROPERTY(SomeSQLVariantColumnOrVariable,'BaseType') function.

    Interesting that you should suggest that Jeff. As I was reading through Hugo's response, I was thinking along the same lines, showing that a Padawan Apprentice eventually learns to think like his Master.

    Thanks, Dwain. It just seemed logical especially since SQL hasn't had the 8,060 barrier for a very long time.

    Nice article, by the way. You've gotten a good discussion going. Well done.

    --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)
    Intro to Tally Tables and Functions

Viewing post 16 (of 15 total)

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