Boolean Magic

By on 8/27/2008

This technique kept being useful in my day to day work over the past few years.  I always have to end up explaining it after a bit of confusion from colleagues who don't understand it when they see it.  So I figured I'd post an explanation that I can refer back to the next time :-)

    ((@regionid is null and 1=1)
    (@regionid is not null and @regionid = rc.regionid))

This WHERE clause essentially allows us to have an optional SQL Parameter without having to resort to dynamically constructed SQL.  If you follow the boolean logic closely you will see that if the @regionid parameter is null (ie. was not supplied), the entire "regionid" clause is thrown out.  This is because 1=1 will of course return true.

If however, the @regionid parameter is not null (ie. was supplied), then we go ahead and compare @regionid to the rc.regionid field.

This technique can provide a simple way of adding "dynamism" (is that a word?) to your application with little effort :-)

See more in the archives