如何正确编写此数据库搜索查询？

Question

我正在尝试编写一个查询，它将根据许多不同的表单字段来搜索数据库。我希望查询按输入的字段进行过滤，而忽略表单中没有输入的任何字段。例如：

表单域：-Project名称-Project Number -Project管理员

因此，如果用户为Project Name输入"Joe“，它应该返回name字段中任何形式的"joe”的所有结果，而不管该记录中的其他值是什么。

我一直试图用严格的SQL来实现这一点，但我得到了不正确的(还没有预料到的)结果。我有一些相当繁重的问题，如果它们太牵强了，我向您道歉。

第一个查询是使用AND语句为所有条件设置的。我意识到它不起作用，因为数据库中的空值仍然不能被通配符拾取。

    <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 + '%' "

我的第二次尝试是检查null值。这样做的效果要好一些，除非您输入"Jim“作为项目经理，它还会返回项目经理值为null的所有记录。

       <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 ) "

有没有办法使用OR并使其作为短路求值，以便在满足第一个条件时不能检查空值？

谢谢，任何关于如何完成我的搜索查询的建议都将是非常好的。

更新：

我在最初的帖子中遗漏了一些有价值的信息，那就是我使用的是一个名为XModPro的DotNetNuke模块。我不知道有什么方法可以成功地使用条件和循环逻辑，我需要用ASP构建查询，因为模块限制您只能使用它们的标记(据我所知，它只提供if和select语句，并使变量的使用比应有的更复杂)。

Answer 1

在构建CommandText变量时，在asp代码中执行一些条件逻辑。例如，如果未在“ProjectName”文本框中输入任何内容，则不要将其包括在查询中。此外，您可以考虑使用OR逻辑而不是AND逻辑。这将取决于您试图实现的目标。

这里有一个如何做的粗略想法。这不是真正的代码，它只是展示了方法：

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

此外，请记住包括查询参数。

Answer 2

这样如何，在临时表中添加所有内容，删除想要的任何内容，然后从临时表中返回。

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