Post reply

What is the best way to dynamically define columns in the WHERE clause and avoid dynamic SQL

  • May 18, 2009 at 3:16 pm

    #996650

    The main purpose of this feature is to detect duplicates within given period of time. Let's say product table has product name and packaging type with effective and expiration date (records can overlap) and one user want to know if there was an effective record for just a product or product and packaging combination.

    The output is always the same: 1) Yes/No. 2) Effective date (NULL if no). 3)Expiration date (NULL if no).

    The example:

    Products

    ProductPackageEff DateExp Date

    Miller Lite6 Pack01/01/200001/01/2099

    Miller Lite12 Pack06/01/200501/01/2099

    Bud Lite12 Pack01/01/200501/01/2008

    Bud Select18 Pack10/10/200001/01/2099

    Shipping

    CompanyShippingTypeEff DateExp Date

    USPSRegular01/01/200001/01/2099

    USPSExpress01/01/200501/01/2099

    FedExRegular01/01/200301/01/2009

    Users will have their search criteria defined in the table specified in the original post.

    As for dynamic SQL, it's just years of bad experience starting with inability to verify syntax and ending with injection attack scare. I don't want to debate the cons and pros of it, I just hope there is a smarter way to do it.

    Thanks,

    IK

  • May 18, 2009 at 5:14 pm

    #996695

    IKIK (5/18/2009)

    As for dynamic SQL, it's just years of bad experience starting with inability to verify syntax and ending with injection attack scare. I don't want to debate the cons and pros of it, I just hope there is a smarter way to do it.

    Dynamic SQL is the smarter way to do it. If you want this much flexibility, then dynamic SQL will be better than any other choice, including writing a client program to do it. That's because it's would still be doing dynamic SQL, it just shifts the dynamc execution to the SQL connection buffer and the dynamic composition to the client code, where injection can still happen, but administrators cannot check for it at run-rime.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 2 posts - 16 through 16 (of 16 total)

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