|
Introduction
Occasionally one discovers a subroutine that greatly simplifies a complex ASP page, transforming spaghetti-like code into something that is easy to maintain. In this article I share one such function used when I need to produce an SQL query from a number of optional criteria posted by the user.
Building an SQL string from optionally posted criteria is tricky. You need to check if the user posted a value since only then is the criteria included in the query. Numeric entries are different from character entries and multiple-selection fields require additional processing to produce a valid SQL query. Fields that might contain single quotes need special attention to prevent syntax errors.
The AddCriteria function (listed later) elegantly encapsulates the logic to build an SQL statement by adding optional criteria only if the user posted data for a field. Here is an example of how one would use it:
The Code
SQL="select * from DataTable"
SQL=AddCriteria(SQL,"Status","=","Status","Chr")
SQL=AddCriteria(SQL,"PCode","=","Code","Num")
SQL=AddCriteria(SQL,"Price",">","LowPrice","Num")
SQL=AddCriteria(SQL,"Price","<","HighPrice","Num")
SQL=AddCriteria(SQL,"Category","in","Catg","Num")
SQL=AddCriteria(SQL,"City","in","City","Chr")
SQL=AddCriteria(SQL,"Name","like","Name","Chr")
The SQL query is started by specifying everything up to the Where clause. Then the AddCriteria function is used to assemble optionally posted values and append it to the SQL expression. The AddCriteria parameters are almost self-explanatory:
Parameter 1: The SQL expression as constructed thus far.
Parameter 2: The database field name for this criterion.
Parameter 3: The SQL comparison operator for this criterion.
Parameter 4: The form field name of the posted value.
Parameter 5: The database field's data type: numeric (Num) or character (Chr).
If one posted the following data to a page containing the code snippet above, the following SQL statement would be generated:
Posted Data:
Status=A&HighPrice=5&Catg=101&Catg=203&Name=Edward
SQL Produced:
select * from DataTable
where
Status = 'A' and Price < 5 and Category in (101,203) and Name like '%Edward%'
Here is the source code for the AddCriteria function:
Function AddCriteria(SQL,DBField,Comparison,FormFld,DataType)
Dim Value,Val,ValArray,ClauseStyle,Connector
AddCriteria=SQL
Value=Request.QueryString(FormFld)
if len(Value)=0 then Exit Function
Comparison=trim(Comparison)
DataType=trim(DataType)
ClauseStyle=ucase(Comparison & ":" & DataType)
select case ClauseStyle
case "=:CHR"
Value="'" & Replace(Value,"'","''") & "'"
case "=:NUM", ">:NUM", "<:NUM"
if (not isNumeric(Value)) then Value=0
case "IN:CHR"
Value=Replace(Value,", ",",")
Value=Replace(Value,"'","''")
Value="('" & Replace(Value,",","','") & "')"
case "IN:NUM"
ValArray=split(Value,",")
Value=""
for each Val in ValArray
if isNumeric(Val) then Value=Value & "," & trim(Val)
next
if len(Value)< 2 then Exit Function
Value=Mid(Value,2) '** remove leading comma
Value="(" & Value & ")"
case "LIKE:CHR"
Value="'%" & Replace(Value,"'","''") & "%'"
case else
Err.Raise 1,"Function AddCriteria",_
("Missing case for '" & ClauseStyle & "'")
end select
Connector=" where " '** for first criterion only
if 0<instr(1,SQL,"where") then Connector=" and "
AddCriteria=SQL & Connector & DBField & Comparison & Value
End Function
What makes this code elegant is how few If...Then logical constructions are needed. I postulate that the number of coding bugs is directly proportional to the number of If clauses employed, particularly ElseIfs. I reduce the number of If statements required in AddCriteria by cleverly combining the comparison operator and the data type so that each case statement closely represents a single type of SQL clause. Also note the use of the Err object to signal when invalid arguments are provided to AddCriteria, rather than defaulting to an empty result which would sometimes be wrong..
You may need to modify AddCriteria to fit your situation. If you are using a POST rather than a GET method in your form, you'll need to replace Request.QueryString with Request.Form. If you have different query styles, you may need to extend AddCriteria by adding Case statements for other comparison and data-type values.
About the Author
Ed Myers is an independent Web developer living in the Dulles Corridor of northern Virginia. He can be reached by email at ed@myersfamily.com or visit his personal Web site at www.myersfamily.com.
|
