SQL Select, sometime need 'Where' +/- 'And' sometimes need neither

0
votes

I have a query to construct Sometimes it will be

strSQL = 'Select * from tableA'

sometimes it will be

strSQL = 'Select * from tableA where FieldA > x'

sometimes it will be

strSQL = 'Select * from tableA where FieldB < y'

and finally sometimes it's

strSQL = 'Select * from tableA where FieldA > x and FieldB < y'

So the 'Where' can be in front of either FieldA or FieldB. I find that this can make for convoluted conditional logic to construct the query string. My usual 'trick' is to put in a 'Where' clause that's true for all records in the table so that fieldA and fieldB, if they are included, will always have an 'And' before them. Something like

strSQL = 'Select * from tableA where FieldC > 0'

if condition1 true

strSQL = strSQL + ' and FieldA > x'

end if

if condition2 true

strSQL = strSQL + ' and FieldA < B'

end if

Is there a better way to achieve this other than using more conditional logic in my code?

3
sql

3 Answers

4
votes

One trick is to use 1=1 as a condition...

strSQL = 'Select * from tableA where 1=1'

Then proceed like you already have above. This will save the DBMS having to actually check any conditions on FieldC (which will always take processing time, even if FieldC is always >0), since 1=1 will be optimised out by the query parser.

1
votes

You can break out something like this to a method

if (first)
{
    first = false;
    return " where ";
}
else
{
    return " and ";
}

and call that method before the real statement in the if-blocks.

edit:

or break out everything to an AppendCondition method that also adds the condition string.

Then you call it like

strSQL = 'Select * from tableA'
if condition1 true
    strSQL = strSQL + AddCondition('FieldA > x')
end if
if condition2 true
    strSQL = strSQL + AddCondition('FieldA < B')
end if
1
votes

In a stored procedure, I sometimes use sql like

select * from tableA 
where (@paramX is null or fieldA > @paramX)
and (@paramY is null or fieldB < @paramY)

where a null value means "don't check this value".