title: Stored procedure search parameter in MS SQL
body: <p>I have got the MS SQL stored procedure, which is designed to return the results in XML showing all the applicants who are <code>'sales'</code>.</p>
  
  <p>There are filters on the page to drill down the results one being a search (<code>sString</code>). </p>
  
  <p>The desired behavior is:</p>
  
  <ul>
  <li>If <code>sString</code> is <code>null</code>, it should return all the results;</li>
  <li>If <code>sString</code> is <code>not null</code>, it should make <code>sString</code> to the show the found fields. </li>
  </ul>
  
  <p>The SP does not work as expected: it always returns all the results regardless of <code>sString</code> value. </p>
  
  <p>MS SQL Code of the SP:</p>
  
  <pre><code>WITH OrderedMatches AS
  (
  
      SELECT ROW_NUMBER() OVER (ORDER BY MB.Member_Registered DESC) AS RowNumber, 
      MB.Member_ID,
      MB.Member_Name, MB.Member_Mobile, MB.Member_Propertytosell, MB.Member_Propertytosell_Details, MB.Member_ExistingBuytoLet, MB.Member_TalkingPoints,
      MB.Member_Registered, MB.Member_Funding, MB.Member_Active, MB.Member_Last_Contacted, MB.Member_Situation
  
      FROM dbo.Member_Base MB INNER JOIN dbo.Member_Criteria MC ON MC.Member_ID = MB.Member_ID
  
      WHERE MB.Member_Active = 1 AND MC.Criteria_Section = 'sales' 
      AND (
          @sType = 'a'
          OR (
              @sType = 'b' AND MB.Member_Propertytosell = 1
          )
          OR (
              @sType = 'c' AND MB.Member_ExistingBuytoLet = 1
          )   
  
      )
      OR (
          MB.Member_Name LIKE '%' + @sString + '%' OR MB.Member_Mobile LIKE '%' + @sString + '%' OR MB.Member_Propertytosell_Details LIKE '%' + @sString + '%'
      )
  
  )       
  
  SELECT 
  (
      SELECT 
          OM.Member_ID as "@id",
          OM.Member_Name as "@appname",
          OM.Member_Mobile as "@contact",
          OM.Member_Propertytosell as "@propts",
          OM.Member_Propertytosell_Details as "@proptsdetails",
          OM.Member_ExistingBuytoLet as "@existingBTL",
          OM.Member_TalkingPoints as "@talkingpoints",
          OM.Member_Registered as "@registered",
          OM.Member_Funding as "@funding",
          OM.Member_Active as "@active",
          OM.Member_Last_Contacted as "@lastcontact",
          OM.Member_Situation as "@situation"
  
      FROM OrderedMatches OM
  
      WHERE OM.RowNumber Between @nstart AND @nend
      FOR XML path ('applicant'), TYPE
  ),
  (
      SELECT COUNT(*)  as "@titems"
      FROM OrderedMatches
      FOR XML path ('meta')
  )
  FOR XML PATH ('')
  
  
  END
  </code></pre>
  
  <p>I suppose the SP is wrong but cant see at which part exactly.</p>
  
  <p>Does anyone have any suggestions?</p>