SQL and or where?



  • Hi, I wrote this little function a year ago. It's for a searchable comments database with MSAccess. When having multiple criteria entered, this would include the WHERE when creating the first SQL statement and AND for every next statements. The more I read this code, the more I think I did a WTF, but I can't spot it. (I know WTF for using an Access DB, but they don't want me to have a local sql server at work)

    Did I done something wrong with this or am I genius?

     Function ANDorWHERE(count As Integer, sql As String) As String
    If count > 0 Then
        ANDorWHERE = " AND "
    Else
        ANDorWHERE = " WHERE "
    End If

    End Function

    [snip]

    'I check every field like this and build the sql statement if the field is not empty

    If hasContent(txtNom) Then
        sql = sql & ANDorWHERE(c, sql) & dep & ".[Nom] LIKE '" & txtNom & "*'"
        c = c + 1
    End If

    [Long snip to eof]

    Oh, and I'm aware that I'm not using stored procedures.



  • First, your andorwhere function has a second argument which is never used

    Second, VB has IIF() for this sort of condional two-value switch, so your function LIKE 'square wheel'.

    Third, txtNom doesn't appear to be between %'s, so LIKE will behave like =, unless it's an Access quirk I'm unaware of.



  • @dhromed said:

    Third, txtNom doesn't appear to be between %'s, so LIKE will behave like =, unless it's an Access quirk I'm unaware of.

    Access, in all of its brillance, uses * as the wildcard, so he's doing prefix searching. I think they used that symbol as the LIKE wildcard in a very early version of the SQL standard, so it's likely a relic from those days.



  • @dhromed said:

    First, your andorwhere function has a second argument which is never used

    I knew something was wrong with my poorly built function!

    @dhromed said:

    Second, VB has IIF() for this sort of condional two-value switch, so your function LIKE 'square wheel'.

     Damn me...

    @dhromed said:

    Third, txtNom doesn't appear to be between %'s, so LIKE will behave like =, unless it's an Access quirk I'm unaware of.

    I wanted to search for all strings starting with text in txtNom to ease finding some particular miswrote names.

    At least, the DB works :D I never had any training to do so.

     



  •  The biggest "WTF" is not using parameters, since if txtNom has single quotes or anything funky it may screw up your SQL.

    Other than that, it is a matter of preference on how to string together dynamic "AND" conditions.  There seems to be 3 ways to do it:

    1) always start your where clause with "1=1 " , and tack on " AND xx" conditions to that.  No need to worry about preceeding ANDs causing an error. 

    2) conditionally add " AND " to all but the first item, as you are doing here.  To me, this is not usually worth it, since there are easier ways.

    3) start your WHERE clause with "", and tack on " AND xxx" conditions, and then do a quick MID() / SUBSTRING() at the end to remove the first "AND".

    I usually go with option #3, for what it is worth.

    One other option is that dynamic conditions don't need to generate dynamic SQL at all. For example, in your case, if NOM is never null, you could just exclude the condition completely and always execute the where clause and it will still work both ways (since a value of "" for txtNom generates LIKE "*", which matches everything anyway).  This keeps things simple.

    You can also use straight-up boolean logic to handle many conditional where clauses as well.

    But please -- look into using parameters, whether or not you are using stored procedures (or, in the case of access, stored "queries"). Even Access fully supports parameterized commands.

    - Jeff



  • Hi again,

    On the DB I'm working on, i'm already using stored queries. But for the one mentionned above, I've only added a quick Replace(variable, "'", "''") to prevent some statement escape. As for now, we are two using this DB for keeping a list of our customers comments, but no one (in staff/admins) use that system. They prefer to see the original paper and give it to us after. Originally, this was for reporting once a week. They should tell me to trash that old DB as I could free my mind.

    I think the WTF is they ordered me to deliver a comment system on time, and nobody use it.

    Jonatan


Log in to reply