[thelist] ASP/SQL Solution for Double Currency Web Site

rudy r937 at interlog.com
Wed Feb 21 20:09:28 CST 2001


> Quick question, can I *still* use WHERE clauses

[ best curly howard voice: ]   soiteny!!


> The current situation .. is building the SQL statement
> based on the items selected in the form (search page).
> So, there are a few IF's and ENDIF's required in order to
> string the SQL statement before actually processing it.

have you heard of the WHERE 1=1 trick?

it's in the tip archive somewhere, but basically, it goes like this --

start your WHERE clause with something that's true

then add AND's without bothering to do the extra test to see if they are
the first or subsequent clauses

this also avoids having a test at the end to see if any of the IFs were
true (if none were true, you wouldn't ordinarily want the WHERE keyword at
all)

sample sql might look like this --

    select Stuff
       from yourTable
       where 1=1
     <cfif activesonly>AND active="Y"</cfif>
     <cfif expediteselected>AND expedite="Y"</cfif>

this trick is only useful when you are not going after specific rows by
key -- in your case would have something like

     where item in ( [ listr of item numbers in cart ] }

and this would take the place of the  1=1  


rudy





More information about the thelist mailing list