Boolean Magic
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 
WHERE
((@regionid is null and 1=1)
OR
(@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
Björn Said,
August 28, 2008 @ 5:57 am
Hm, I do not know SQL too well but shouldn’t something like (@regionid is null OR @regionid = rc.regionid) result in the same? I mean how’s (true and true) OR (not evaluated) different from (true OR not evaluated)?
Joel Martinez Said,
August 28, 2008 @ 10:02 am
well played, you know what … I think you’re right Björn
you get the prize!
Peter Said,
September 1, 2008 @ 2:50 am
Second Björn on that, (@regionid is null OR @regionid = rc.regionid) is the way to go. If @regionid is null or missing then (@region is null) evaluates to true. If @regionid is not null, the second part will be evaluated.