在存储过程中从可选参数搜索到SQL查询的最佳方法是什么? [英] What is best way to search from optional parameter to SQL query in a stored procedure?

问题描述

当涉及到SQL存储过程中带有可选参数的搜索记录时，这两个查询除外.两者都返回相同的结果.考虑到性能，您将使用哪一个?为什么?

When it comes to search record with optional parameter in SQL stored procedure, out of these two queries. Both return the same results. Considering performance, which one will you use and why?

我有一个存储过程，该过程具有多个搜索参数，并且将在多个表中进行搜索，并具有庞大的记录集的联接.

I have a stored procedure which has multiple search parameters and will be searching in multiple tables, with joins from a huge record set.

DECLARE @EventName VARCHAR(100)
--SET @EventName = ''
--SET @EventName = NULL
SET @EventName = 'Coffee in Coffee Bean'

-- Query - 1
SELECT * 
FROM EventDetails
WHERE 
    1 = CASE 
           WHEN @EventName IS NULL OR @EventName = '' THEN 1 
           WHEN EventName LIKE '%'+ @EventName +'%' THEN 1 ELSE 0 
        END

-- Query - 2
SELECT * 
FROM EventDetails
WHERE 
    EventName LIKE '%' + CASE 
                            WHEN LEN(LTRIM(@EventName)) > 0 
                               THEN @EventName 
                               ELSE EventName 
                         END + '%' 

推荐答案

如果您不考虑动态SQL，请尝试使用尽可能少的函数和数据篡改.最常见的方法如下:

If you can't consider dynamic SQL, then try to use as less functions and data tampering as possible. The most common approach for this would be something like the following:

DECLARE @OptionalFilter1 INT
DECLARE @OptionalFilter2 VARCHAR(100)
-- ...
DECLARE @OptionalFilterN BIT

SELECT
    YourColumns
FROM
    YourTable AS T
WHERE
    (@OptionalFilter1 IS NULL OR @OptionalFilter1 = T.Filter1Column) AND
    (@OptionalFilter2 IS NULL OR @OptionalFilter2 = T.Filter2Column) AND
    -- ...
    (@OptionalFilterN IS NULL OR @OptionalFilterN = T.FilterNColumn)

您的示例为:

DECLARE @EventName VARCHAR(100) = 'Coffee in Coffee Bean'

SELECT
    * 
FROM 
    EventDetails AS E
WHERE
    (@EventName IS NULL OR E.Event LIKE '%' + @EventName + '%')

如果这将在过程中结束，请考虑在查询中使用OPTION (RECOMPILE)并使用许多过滤器和/或将存储过程的参数分配给新的局部作用域变量，以防止参数嗅探.您可以阅读有关参数嗅探问题的信息(与您的示例类似)

If this is gonna end in a procedure, consider using OPTION (RECOMPILE) on the query with many filters and/or assigning the stored procedure parameters to new local scope variables to prevent parameter sniffing. You can read about parameter sniffing problem (with an example similar to yours) in this post.

这篇关于在存储过程中从可选参数搜索到SQL查询的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！