多标准存储过程最佳实践/模式

Question

以下存储过程是可行的，但我想知道--在SQL Server中处理这种类型的操作是否有最佳实践/模式？本质上，我传入了两个不同的参数。根据传入的“criteria”参数，我运行一个带有一些特定条件的查询语句--谢谢。

    ALTER PROCEDURE [dbo].[Select_Project_Info_By_Value]
    (
    @value VarChar(50), 
    @criteria VarChar(50)
    )

    AS
    BEGIN
        SET NOCOUNT ON;

    if @criteria= 'All'
        SELECT     some some tables...
        FROM         dbo.Table1
        WHERE     (NOT (Status = 'ABC'))

    else if @criteria = 'X'
        SELECT     some some tables...
        FROM         dbo.Table1
        WHERE     (Matno LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))

    else if @criteria = 'Y'
        SELECT     some some tables...
        FROM         dbo.Table1
        WHERE     (ID LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))

    else if @criteria = 'Z'
        SELECT     some some tables...
        FROM         dbo.Table1
        WHERE     (ProDescr LIKE '%' + @value + '%')

    else if @criteria = 'A'
        SELECT     some some tables...
        FROM         dbo.Table1
        WHERE     (CustCode LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))

    else if @criteria = 'B'
        SELECT     some some tables...
        FROM         dbo.Table1
        WHERE     (ApplName LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))

    END

- shawno

Answer 1

或许是这样的：

SELECT some some tables...
FROM         
   dbo.Table1
WHERE
  (
      @criteria= 'All'
      AND (NOT (Status = 'ABC'))
   )
   OR
   (
      @criteria = 'X'
      AND (Matno LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))
   )
   OR
   (
       @criteria = 'Y'
       AND (ID LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))
   )
   OR
   (
       @criteria = 'Z'
       AND (ProDescr LIKE '%' + @value + '%')
   )
   OR
   (
       @criteria = 'A'
       AND (CustCode LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))
   )
   OR
   (
       @criteria = 'B'
       AND (ApplName LIKE '%' + @value + '%') AND (NOT (Status = 'ABC'))
   )

- Arion

---

我认为如果有太多的条件或者运算符会导致性能问题。 - Adam

Answer 2

Erland Sommarskog撰写了关于where子句和动态搜索条件中可选参数的权威作品。本文详细介绍了每种方法的优缺点（其中有许多）。该链接适用于SQL 2005及更早版本。对于2008年版本，请使用此链接（http://www.sommarskog.se/dyn-search-2008.html）

如果我是你，我会阅读这篇文章并选择一种方法，尽管无论你如何编写查询，都无法真正优化它。你的LIKE '%' + @value + '%'查询无法使用索引，因此您将始终执行表扫描。

在这种情况下，您可能会遇到参数嗅探问题，因为不同的输入参数可能会产生非常不同的查询计划或代码路径。该存储过程可能是WITH RECOMPILE选项的一个很好的候选对象。

- Code Magician