How can I correctly write this database search query?

Question

I'm attempting to write a query that will search the database based on many different form fields. I want the query to filter by fields that are entered and simply disregard any fields that are not entered on the form. For example:

Form Fields: -Project Name -Project Number -Project Manager

So if the user enters a "Joe" for Project Name, it should return all results with any form of "joe" in the name field regardless of what the other values are in that record.

I've been trying to accomplish this with strictly SQL and I'm having incorrect (yet expected) results. I've got some pretty hefty queries, and I apologize if they're pretty far fetched.

This first query was set up using AND statements for all the conditions. I realized that it doesn't work, as NULL values in the database still don't get picked up by wildcards.

    <ListDataSource CommandText="
SELECT [surveyid], 
       [projectnumber], 
       [siteno], 
       [person], 
       [subatpdate], 
       [projectname], 
       [priorsurveyor], 
       [siteaddress], 
       [sitecity], 
       [sitestate], 
       [sitezip], 
       [survey], 
       [zoning], 
       [environmental], 
       [emg_projectnumber], 
       [zoning_projectnumber], 
       [surveyor_projectnumber] 
FROM   tblrawprojectdatabase 
WHERE  [projectnumber] LIKE '%' + @projectnumber + '%' 
       AND [siteno] LIKE '%' + @siteno + '%' 
       AND [person] LIKE '%' + @person + '%' 
       AND [subatpdate] LIKE '%' + @subatpdate + '%' 
       AND [projectname] LIKE '%' + @projectname + '%' 
       AND [priorsurveyor] LIKE '%' + @priorsurveyor + '%' 
       AND [siteaddress] LIKE '%' + @siteaddress + '%' 
       AND [sitecity] LIKE '%' + @sitecity + '%' 
       AND [sitestate] LIKE '%' + @sitestate + '%' 
       AND [sitezip] LIKE '%' + @sitezip + '%' 
       AND [survey] LIKE '%' + @survey + '%' 
       AND [zoning] LIKE '%' + @zoning + '%' 
       AND [environmental] LIKE '%' + @environmental + '%' 
       AND [emg_projectnumber] LIKE '%' + @emg_projectnumber + '%' 
       AND [zoning_projectnumber] LIKE '%' + @zoning_projectnumber + '%' 
       AND [surveyor_projectnumber] LIKE '%' + @surveyor_projectnumber + '%' "

My second attempt checks for null values. This works a little better, except if you enter "Jim" as the project manager, it also returns all records with null as a project manager value.

       <ListDataSource CommandText="
SELECT [surveyid], 
       [projectnumber], 
       [siteno], 
       [person], 
       [subatpdate], 
       [projectname], 
       [priorsurveyor], 
       [siteaddress], 
       [sitecity], 
       [sitestate], 
       [sitezip], 
       [survey], 
       [zoning], 
       [environmental], 
       [emg_projectnumber], 
       [zoning_projectnumber], 
       [surveyor_projectnumber] 
FROM   tblrawprojectdatabase 
WHERE  ( [projectnumber] LIKE '%' + @projectnumber + '%' 
          OR [projectnumber] IS NULL ) 
       AND ( [siteno] LIKE '%' + @siteno + '%' 
              OR [siteno] IS NULL ) 
       AND ( [person] LIKE '%' + @person + '%' 
              OR [person] IS NULL ) 
       AND ( [priorsurveyor] LIKE '%' + @priorsurveyor + '%' 
              OR [priorsurveyor] IS NULL ) 
       AND ( [siteaddress] LIKE '%' + @siteaddress + '%' 
              OR [siteaddress] IS NULL ) 
       AND ( [sitecity] LIKE '%' + @sitecity + '%' 
              OR [sitecity] IS NULL ) 
       AND ( [sitestate] LIKE '%' + @sitestate + '%' 
              OR [sitestate] IS NULL ) 
       AND ( [sitezip] LIKE '%' + @sitezip + '%' 
              OR [sitezip] IS NULL ) 
       AND ( [survey] LIKE '%' + @survey + '%' 
              OR [survey] IS NULL ) 
       AND ( [zoning] LIKE '%' + @zoning + '%' 
              OR [zoning] IS NULL ) 
       AND ( [environmental] LIKE '%' + @environmental + '%' 
              OR [environmental] IS NULL ) 
       AND ( [emg_projectnumber] LIKE '%' + @emg_projectnumber + '%' 
              OR [emg_projectnumber] IS NULL ) 
       AND ( [zoning_projectnumber] LIKE '%' + @zoning_projectnumber + '%' 
              OR [zoning_projectnumber] IS NULL ) 
       AND ( [surveyor_projectnumber] LIKE '%' + @surveyor_projectnumber + '%' 
              OR [surveyor_projectnumber] IS NULL ) "

Is there someway to use OR and make it work as a short circuit evaluation so that it can't check for nulls if the first condition is met?

Thank you, any recommendations on how to accomplish my search query would be excellent.

Update:

Some valuable information I left out in the initial post is the fact I'm using a DotNetNuke Module called XModPro. I am not aware of any way to successfully use the conditional and looping logic I would need to build the query with ASP as the module restricts you to using their tags (which as far as I see, only provides if and select statements and makes variable use more complex than it should be)

Answer 1

What about this, add everything in temp table delete whatever you want and return from temp table.

SELECT [surveyid], [projectnumber], [siteno], [person], [subatpdate], [projectname], 
[priorsurveyor], [siteaddress], [sitecity], [sitestate], [sitezip], [survey], [zoning], 
[environmental], [emg_projectnumber], [zoning_projectnumber], [surveyor_projectnumber] 
INTO #temp
FROM tblRawProjectDatabase
 WHERE ([projectnumber] LIKE '%' + @projectnumber + '%' OR [projectnumber] IS NULL) AND 
([siteno] LIKE '%' + @siteno + '%' OR [siteno] IS NULL) AND 
([person] LIKE '%' + @person + '%' OR [person] IS NULL) AND 
([priorsurveyor] LIKE '%' + @priorsurveyor + '%' OR [priorsurveyor] IS NULL) AND 
([siteaddress] LIKE '%' + @siteaddress + '%' OR [siteaddress] IS NULL) AND 
([sitecity] LIKE '%' + @sitecity + '%' OR [sitecity] IS NULL) AND 
([sitestate] LIKE '%' + @sitestate + '%' OR [sitestate] IS NULL) AND 
([sitezip] LIKE '%' + @sitezip + '%' OR [sitezip] IS NULL) AND 
([survey] LIKE '%' + @survey + '%' OR [survey] IS NULL) AND 
([zoning] LIKE '%' + @zoning + '%' OR [zoning] IS NULL) AND 
([environmental] LIKE '%' + @environmental + '%' OR [environmental] IS NULL) AND 
([emg_projectnumber] LIKE '%' + @emg_projectnumber + '%' OR [emg_projectnumber] IS NULL) AND 
([zoning_projectnumber] LIKE '%' + @zoning_projectnumber + '%' OR [zoning_projectnumber] IS NULL) AND 
([surveyor_projectnumber] LIKE '%' + @surveyor_projectnumber + '%' OR [surveyor_projectnumber] IS NULL)

DELETE FROM #temp WHERE ProjectManaer IS NULL
SELECT * FROM #temp
DROP TABLE #temp

Answer 2

Do some conditional logic in your asp code as you build your CommandText variable. For example, if nothing is entered in the ProjectName text box, do not include it in your query. Also, you might consider using OR logic instead of AND. This would depend on what you are trying to achieve.

Here is a rough idea of how to do it. It's not real code, it just shows the approach:

string WhereClause = "where 1 = 2 "
if a project name was specified.
WhereClause = WhereClause + something about the project name.
etc

Also, remember to include query parameters.