alternative to coalesce?

  • I am writing a stored procedure with optional variables, but am not sure the best way to represent if a variable is filled in or not without repeating the SQL with and without each parameter.

    I can't use Coalesce because my system's database doesn't allow NULL on the columns in question. Our applications default the columns to a blank value if data is not provided. So I need to query matching the variable to the criteria the the stored proc receives, or if none is received to disregard that column in the where clause, basically. Is that doable?

  • Coalesce or IsNull can still work, since the variable/parameter will have no value assigned.

    The problem with this method, or similar ones, is that it tends to kill performance because of non-SARGability of the query.

    Dynamic SQL is often the best answer for this.

    - 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

  • Have a look here for more information:

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    In particular, look at the way that dynamic SQL is used but it's still parameterised (avoiding the risk of SQL Injection).

    Alternatively, I believe that SQL 2008 SP2 finally contains the fix for static parameter evaluation if you add a RECOMPILE hint to the query, then you can use something along the lines of:

    column = @Variable or @Variable is NULL without compromising performance, which is a more elegant solution.

    EDIT:

    Updated with link to more information:

    http://www.sommarskog.se/dyn-search-2008.html#SPandCUs

  • In the past, I have used a combination of IF statements to EXECUTE other procedures with the correct SQL statement (based on usage and search criteria) for specific statements (each stored in a separate procedure) and a dynamic SQL procedure as the final catch all remaining requests.

    I prefer the specific statements (not dynamic SQL) as they are relatively easy to change and tweak, where as dynamic SQL can be a bit of pain.

    The dynamic SQL procedure would normally account for 5-10% of the traffic and the remainder going to the specific procedures. If the dynamic statement started to grow above that, it was time to investigate and perhaps and a little more to the controlling IF statement.

  • Thanks Howard, the dynamic SQL from that blog is just what I needed.

  • When there is no data to fill out the columns in our apps, they are left as they are. It is imperative that I either match the variable to the stored procedure's arguments or omit it if no parameters were received. Do you think that is possible?

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    quordle

Viewing 6 posts - 1 through 6 (of 6 total)

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