building dynamic SQL string from multiple search values

[Steven Scaife]

Ok I have to create on the fly a dynamic search query when the user
completes a form, this could have from 1 up to about 15 search values. Is
there any pages or sites that recommend a good way to do it. currently i do
it the following way

'set aWhere to false so we know when to add the and into the SQL statement
aWhere = "False"

'builds the SQL string

strSQL = "SELECT CMPNY_NO, CMPNY_NAME, PREV_CMPNY_NAME, REG_POSTCODE,
STATUS, TRAD_POSTCODE, TELEPHONE FROM Company WHERE "


IF txtComp <> "" then
strSQL = strSQL & "(CMPNY_NAME like '" & txtComp & "%' OR PREV_CMPNY_NAME
like '" & txtComp & "%') "
aWhere = "True"
END IF

IF CoNo <> "" then
IF aWhere = "True" then
strSQL = strSQL & "AND (CMPNY_NO like '" & CoNo & "%') "
ELSE
strSQL = strSQL & "(CMPNY_NO like '" & CoNo & "%') "
aWhere = "True"
END IF
END IF

IF regPcode <> "" THEN
IF aWhere = "True" then
strSQL = strSQL & "AND (REG_POSTCODE like '" & regPcode & "%') "
ELSE
strSQL = strSQL & "(REG_POSTCODE like '" & regPcode & "%') "
aWhere = "True"
END IF
END IF

IF tradPcode <> "" then
IF aWhere = "True" then
strSQL = strSQL & "AND (TRAD_POSTCODE like '" & tradPcode & "%') "
ELSE
strSQL = strSQL & "(TRAD_POSTCODE like '" & tradPcode & "%') "
aWhere = "True"
END IF
END IF

IF telNo <> "" then
IF aWhere = "True" then
strSQL = strSQL & "AND (TELEPHONE like '" & telNo & "%') "
ELSE
strSQL = strSQL & "(TELEPHONE like '" & telNo & "%') "
aWhere = "True"
END If
END IF

'response.write strSQL & "<br><br>"
'response.end

set RS = conn.execute(strSQL)

Only problem is I don't think this is the most efficient way of doing it. I
have been commissioned to re-write the program (currently written in C++)
into ASP, there are several pages that build a SQL string from the form
inputs. The database is SQL server 2000, can anyone suggest a better way or
point me in the right direction.

thanks in advance for any help

[Bob Barrows [MVP]]

Steven Scaife wrote:

> Ok I have to create on the fly a dynamic search query when the user
> completes a form, this could have from 1 up to about 15 search
> values. Is there any pages or sites that recommend a good way to do
> it. currently i do it the following way
>
> 'set aWhere to false so we know when to add the and into the SQL
> statement aWhere = "False"
>
> 'builds the SQL string
>
> strSQL = "SELECT CMPNY_NO, CMPNY_NAME, PREV_CMPNY_NAME, REG_POSTCODE,

Stop right there. If performance is your goal, then you cannot contemplate
using dynamic sql. Pass the values to a stored procedure and process them
there. Here is some information about doing dynamic search conditions:
[url]http://www.sommarskog.se/[/url]

Just click the "Dynamic Search Conditions" link.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"