SQL过滤查询

Question

我有一个有多个字段的表格。我正在尝试在asp.net中创建搜索过滤器，以便用户可以通过一个或多个字段进行搜索。因此，基本上我想创建一个单个存储过程，它接收4个参数，并且如果其不为null，则将该参数附加到WHERE子句中...

TableExample有4列,Col1 Col2 Col3 Col4

我希望能够用一个单独的存储过程完成这个操作，而不必为每种可能的组合都创建一个新的存储过程。

我正在尝试如下的方式，虽然是错误的，但这是我目前所得到的。

谢谢！

CREATE PROCEDURE [dbo].[Search]
    @Col1 int,
    @Col2 int,
    @Col3 int,
    @Col4 int
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT *

FROM
    [dbo].[TestTable]
WHERE
    1=1
    CASE        
        WHEN @Col1 IN NOT NULL
        THEN AND [Col1] = @Col1

        WHEN @Col2 IN NOT NULL
        THEN AND [Col2] = @Col2

        WHEN @Col3 IN NOT NULL
        THEN AND [Col3] = @Col3

        WHEN @Col4 IN NOT NULL
        THEN AND [Col4] = @Col4
    END

- Gabe

---

那么，到底应该如何达成共识呢？是在C#代码中构建查询字符串然后执行查询，还是尝试动态构建存储过程？似乎使用存储过程的方法存在一些缺陷... - Gabe

Answer 1

利用 OR 短路的事实。我假设 -1 不是一个有效的值。

CREATE PROCEDURE [dbo].[Search]
    @Col1 int = -1,
    @Col2 int = -1,
    @Col3 int = -1,
    @Col4 int = -1
AS
Begin
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT *

FROM
    [dbo].[TestTable]
WHERE
 (@Col1 = -1 OR [Col1] = @Col1)
and
(@Col2 = -1 OR [Col2] = @Col2)
and
(@Col3 = -1 OR [Col3] = @Col3)
and
(@Col4 = -1 OR [Col4] = @Col4)



END

- cmsjr

---

这是我一段时间以来最喜欢的搜索方法。但后来我意识到，对于某些搜索参数，它可能会导致糟糕的执行计划和低效的性能。 - Mladen Prajdic

这是一种很好的方法，可以在不使用动态SQL的情况下完成，因为您事先知道列名。您甚至可以使用原始的NULL值来完成此操作，只需将WHERE行更改为“（@Col1 IS NULL OR [Col1] = @Col1）”，这将使-1成为有效值。 - SqlRyan

不使用 null 来表示整数只是我的习惯 ;) - cmsjr

Answer 2

搜索是我推荐使用动态SQL或在代码中构建SQL字符串的罕见选项之一。如果您使用的是所有存储过程环境，请在存储过程中使用动态SQL。将其参数化并使用sp_executeSQL运行它，以避免SQL注入。

- Mladen Prajdic

Answer 3

你可以使用类似于你已有的方法来实现它：

WHERE
  CASE
    WHEN @Col1 IS NULL THEN true
    ELSE [Col1] = @Col1
  END
AND
  CASE
    WHEN @Col2 IS NULL THEN true
    ELSE [Col2] = @Col2
  END
...

或者你可以使它更简单，虽然可能不太易读。

WHERE (@Col1 IS NULL OR [Col1] = @Col1])
  AND (@Col2 IS NULL OR [Col2] = @Col2])
  AND ...

- Welbog

Answer 4

你需要使用动态SQL来实现：

CREATE PROCEDURE [dbo].[Search]
    @Col1 int,
    @Col2 int,
    @Col3 int,
    @Col4 int
AS

DECLARE @SQL nvarchar(MAX)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET @SQL = 'SELECT * 
              FROM [dbo].[TestTable]
             WHERE 1=1 '

IF @Col1 IS NOT NULL
SET @SQL = @SQL + ' AND Col1 = ''' + @Col1 + ''' '

IF @Col2 IS NOT NULL
SET @SQL = @SQL + ' AND Col2 = ''' + @Col2 + ''' '

IF @Col3 IS NOT NULL
SET @SQL = @SQL + ' AND Col3 = ''' + @Col3 + ''' '

IF @Col4 IS NOT NULL
SET @SQL = @SQL + ' AND Col4 = ''' + @Col4 + ''' '

exec sp_executesql @SQL

END

请记住，这样做存在危险，包括 SQL 注入以及一系列其他权限问题的出现，因为它是动态 SQL，但这是在数据库层面上实现此目标的唯一方法。如果您想在应用程序层面（使用 C#）构建查询，则可以更全面地防御 SQL 注入攻击。

以下是一些可能帮助您了解缺点的动态 SQL 链接：

http://www.sommarskog.se/dynamic_sql.html http://slashstar.com/blogs/tim/archive/2006/10/12/The-Prevalence-and-Dangers-of-SQL-Injection.aspx

- SqlRyan

Answer 5

感谢大家的回复。但是，我有点不同的做法。希望能对某些人有所帮助！以下是我的做法：

CREATE PROCEDURE [dbo].[TestTable_Search]
    @Col1 int,
    @Col2 uniqueidentifier,
    @Col3 datetime,
    @Col4 datetime
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT *

FROM
    [dbo].[TestTable]
WHERE
    [Col1] = COALESCE(@Col1, Col1) AND
    [Col2] = COALESCE(@Col2, Col2) AND
    [Col3] >= COALESCE(@Col3 + "00:00:00", Col3) AND
    [Col4] <= COALESCE(@Col4 + "23:59:59", Col4)

- Gabe