title: How can I Implement sql search functionality in a Stored procedure (Sql Server 2008)
body: <p>I need to write a stored procedure that will search a table based on optional parameters Using sql server 2008.</p>
  
  <p>There will be two modes </p>
  
  <ul>
  <li>basic search mode (we just pass some text)</li>
  <li><p>advanced search mode (Optional parameters are used NOT SearchText is used.)</p>
  
  <p>For testing I am using the AdventureWorks.Person.Contact table</p>
  
  <p>Would you write something like below <strong>if not</strong> can you make suggestions how to improve it?
  Thanks a lot</p>
  
  <pre><code>ALTER PROCEDURE SearchPeople
  @SearchText nvarchar(200)=NULL,   --- only used in basic search mode
  @SearchMode bit,
  @FirstName varchar(50)=NULL,
  @LastName varchar(50)=NULL,
  @EmailAddress varchar(50)=NULL,
  @Phone nvarchar(25)=NULL
  </code></pre></li>
  </ul>
  
  <p>AS </p>
  
  <pre><code>IF @SearchMode=0
      BEGIN   
      print 'BASIC SEARCH'
          SELECT *
          FROM   [Person].[Contact] 
          WHERE  (FirstName LIKE '%' + @SearchText + '%' 
          OR LastName LIKE '%' + @SearchText + '%'
          OR EmailAddress LIKE '%' + @SearchText + '%'
          OR Phone LIKE '%' + @SearchText + '%')
  
      END
  
  ELSE
     BEGIN    
  
      print 'ADVANCED SEARCH'
  
          SELECT *
          FROM   [Person].[Contact] 
          WHERE  (FirstName =@FirstName OR @FirstName IS NULL) 
          AND (LastName =@LastName OR @FirstName IS NULL) 
          AND (EmailAddress =@EmailAddress OR @EmailAddress IS NULL) 
          AND (Phone =@Phone OR @Phone IS NULL) 
  
      END
  </code></pre>