Monday, April 27, 2009

Chaining SQL Record Filters

So, I’ve actually been using this technique for a while, but recently on a project I have been working on I again ran into the need for it.  Many times creating an application you might find yourself with a bunch of checkboxes (or anything for that matter) that you might use to build a SQL where clause, such as ...

select field1, field2, field3
from table
where
    checkbox1 = 0
    and checkbox2 = 1
    and checkbox3 = 1

The problem is that when you are building the where clause you have to check to see if you are on the first item.  If so, then do not add an "and", otherwise add the "and".  Or say you have a static SQL query that you often use and you want to quickly comment out the first item in the where clause but you don’t want to totally delete it.  You may have to do this …

select field1, field2, field3
from table
where
    --checkbox1 = 0
    /*and*/ checkbox2 = 1
    and checkbox3 = 1

I find this to be somewhat awkward to do.  If you are dynamically building a where clause it adds more code to have to decipher, or if you have a static query it adds unneeded edits.  I prefer the below method ...

select field1, field2, field3
from table
where
    1 = 1
    and checkbox1 = 0
    and checkbox2 = 1
    and checkbox3 = 1

By adding the "1 = 1" I can simply add every piece of the where clause with the “and” in front and I don’t have check if I am on the first item or not.  Also it makes it easy to selectively comment out parts of my static query without having to do much typing as the below example shows.

select field1, field2, field3
from table
where
    1 = 1
    --and checkbox1 = 0
    and checkbox2 = 1
    --and checkbox3 = 1

For me this has been very helpful in many projects, including my current one.  Maybe somebody else will find this approach helpful as well.

1 comment:

Robert said...

What? I don't get it. you need to take a break and look at one of these for a while: http://www.google.com/products?sourceid=navclient&rlz=1T4TSHB_enUS309US224&q=disney+neon+wall+clock&um=1&ie=UTF-8&ei=PpX8SaWqNaXutQOv0b3zAQ&sa=X&oi=product_result_group&resnum=4&ct=title