title: Stored procedure for advanced search
body: <p>My Table:</p>
  
  <p><strong>CR_DETAILS</strong></p>
  
  <pre><code>CR_REF_NO VARCHAR(60), ORIGIN_ID INT, DATE_RECEIVED DATETIME, STATUS_ID INT
  </code></pre>
  
  <p>I want to develop a stored procedure to return advanced search results:</p>
  
  <p>There exists search page with the fields mentioned in the above table. User can fill as many columns he is sure.</p>
  
  <p>For the above table, to write stored procedure, some requirements are also there like below:
  There exists a dropdownlist opposite to <code>DATE_RECEIVED</code> field with (&lt;, >, =) options.</p>
  
  <p>User enters some value against to <code>DATE_RECEIVED</code> field and he can select on of the operator. Then the search results should satisfy the condition mentioned.</p>
  
  <p>Can anybody please suggest me how can I do this?</p>
  
  <p>As of now, I have written the following stored procedure, but not working :(</p>
  
  <pre><code>ALTER PROCEDURE usp_AdvancedSearchCR
      @CR_REF_NO VARCHAR(35),
      @ORIGIN_ID INT,
      @DATE_RECEIVED DATETIME,
      @DATE_RECEIVED_COMPARE_STYLE CHAR,
      @STATUS_ID INT
  AS
  BEGIN
      SELECT * FROM CR_DETAILS WHERE
          ((CR_REF_NO LIKE '%' + @CR_REF_NO + '%') OR (CR_REF_NO IS NULL)) AND
          ((ORIGIN_ID = @ORIGIN_ID) OR (@ORIGIN_ID = 0)) AND
          --I am not understanding how to write code for DATE_RECEIVED
          ((STATUS_ID = @STATUS_ID) OR (@STATUS_ID = 0))
  END
  </code></pre>
  
  <p>While testing the stored procedure, I have not entered any value for <code>DATE_RECEIVED</code> and tested, but I am not getting any results. Hence I understood that my other code is also wrong.</p>
  
  <p>Please suggest me, it is urgent for me.</p>