避免在返回搜索结果的存储过程中进行分支？

发布于 2024-09-06 18:13:54 字数 250 浏览 5 评论 0原文

我有一个应用程序需要从 SQL Server 2008 数据库返回搜索结果。我想使用单个存储过程来返回结果，但我发现当我构建存储过程时，它充满了许多 Else .. Else If 语句，其中的查询一遍又一遍地重复，根据用户的搜索略有不同标准。

有更好的方法来解决这个问题吗？我试图避免编写动态 SQL，因为我想要执行计划的好处，但我认为必须有更好的方法。有没有人有任何建议或示例来说明如何最好地设计一个必须处理许多搜索参数（其中许多可能为 NULL）的存储过程？谢谢。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乄_柒ぐ汐 2024-09-13 18:13:54

并不真地。

对于具有语句级重新编译的 SQL Server 2005 及更高版本，OR 子句的损失较小，只是维护复杂性。

使用 Richard Harrison 的方法会使情况变得更糟，因为 OR 不可控制，运行缓慢，很可能不会使用索引。

动态 SQL 带来了 SQL 注入、引用和缓存问题。

这使得 sp_executesql 按照 CountZero 的答案，仍然需要构建字符串。

该解决方案可能不是基于代码的...您真的需要一次搜索所有字段吗？我会尝试将搜索分为简单搜索和高级搜索，或者找出最常见的搜索并尝试涵盖这些查询。

回复收藏 0 原文

梅倚清风 2024-09-13 18:13:54

我总是通过使用默认值和条件来完成此操作；例如

CREATE PROCEDURE [dbo].[searchForElement]
(
    @Town     nvarchar(100) = '',
    @County   nvarchar(100) = '',
    @postcode nvarchar(100) = ''
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT <fields> 

    FROM table 
    WHERE 
        (@Town = ''             OR Town     LIKE '%'+@Town+'%')
        AND (@County = ''       OR County   LIKE '%'+@County+'%')
        AND (@postcode = ''     OR postcode LIKE '%'+@PostCode +'%')
END

编辑：

正如 @gbn 正确建议的那样，上述内容将导致索引扫描，这对于大型表来说可能是一个问题。如果这是一个问题，解决方案是下面使用 ISNULL ，并且将 NULL 添加到任何内容都会导致 NULL ，这将允许索引查找因为优化器可以理解“%”（在 SQL2008 上测试）。这可能可读性较差，但可以更好地利用索引。

CREATE PROCEDURE [dbo].[searchForElement]
(
    @Town     nvarchar(100) = NULL,
    @County   nvarchar(100) = NULL,
    @postcode nvarchar(100) = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT <fields> 

    FROM table 
    WHERE   Town     LIKE ISNULL('%'+@Town+'%', '%')
       AND  County   LIKE ISNULL('%'+@County+'%', '%')
       AND  Postcode LIKE ISNULL('%'+@PostCode +'%', '%')
END

I've always done this by using default values and conditions; e.g.

CREATE PROCEDURE [dbo].[searchForElement]
(
    @Town     nvarchar(100) = '',
    @County   nvarchar(100) = '',
    @postcode nvarchar(100) = ''
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT <fields> 

    FROM table 
    WHERE 
        (@Town = ''             OR Town     LIKE '%'+@Town+'%')
        AND (@County = ''       OR County   LIKE '%'+@County+'%')
        AND (@postcode = ''     OR postcode LIKE '%'+@PostCode +'%')
END

Edit:

As @gbn correctly advises the above will result in an index scan which may be a problem for large tables. If this is a problem the solution is to below using ISNULL and the fact that adding NULL to anything results in NULL it will allow an index seek because the '%' is understood by the optimiser (tested on SQL2008). This may be less readable but it makes better use of the indexes.

CREATE PROCEDURE [dbo].[searchForElement]
(
    @Town     nvarchar(100) = NULL,
    @County   nvarchar(100) = NULL,
    @postcode nvarchar(100) = NULL
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT <fields> 

    FROM table 
    WHERE   Town     LIKE ISNULL('%'+@Town+'%', '%')
       AND  County   LIKE ISNULL('%'+@County+'%', '%')
       AND  Postcode LIKE ISNULL('%'+@PostCode +'%', '%')
END

回复收藏 0 原文