Stack Exchange
Multi-Criteria stored procedure best practice/pattern
The following stored procedure works, but I'm wondering -- is there a best practice/pattern for dealing with this type of action in SQL Server? In essence, I am passing in two different parameters.  Based upon what is passed in for the "criteria" parameter I run a query with some specific conditions in the where clause -- Thanks.
    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


Maybe something like this:
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'))
   )


i think that if so much condition OR can lead to performance issue.

The definitive work of optional parameters in where clauses and dynamic search conditions has been written by Erland Sommarskog. This article details the advantages and disadvantages of each approach (of which there are many) That link is for SQL 2005 and earlier. For 2008, use this link (http://www.sommarskog.se/dyn-search-2008.html)
If I were you, I would review the article and choose an approach although your query cannot really be optimized no matter how you write it. Your LIKE '%' + @value + '%' queries cannot use an index and you will always be performing table scans. 
In this sort of situation, you may run into issues with parameter sniffing since different input parameters could yield very different query plans or paths through the code. This proc may be a good candidate for the WITH RECOMPILE option.  
