title: SQL Complex Search Query With Multiple Value
body: <p>I'm trying to achieve something that Im not sure how to do it. I'm working on a website based on ASP Classic and SQL 2008, IIS7. In search page I have option to have couple value per query like <code>default.asp?q=one|two|three&amp;q2=four|five|six</code> .<br>
  What Im trying to do is to use wildcard in case my queries dont have value and have ability to search couple word in one column at the same time in case my query has couple value. Here are what I want to do:  </p>
  
  <ol>
  <li>If query has value then go fine that in database (Which is pretty simple)</li>
  <li>If query has couple value separated with "|" sign then split them and search for all of them.</li>
  <li>If query does not have value at all, then use wildcard and do not search
  for anything.</li>
  </ol>
  
  <p>So to achieve this I've tried couple thing but didn't get any success. Here is the example of my code. </p>
  
  <p>One -----------  </p>
  
  <pre><code>@style nVarchar(150) = ''
  Select * FROM mytable WHERE CONTAINS(style, @style) AND ...
  </code></pre>
  
  <p>I can manage to split the query to something like <code>'"* value1 *" OR "* value2 *"'</code> to get the best result out of CONTAINS but This one is working if "style" has value. If style does not have value there is no wildcard so I can retrive all data without filtering down the database.</p>
  
  <p>Two ---------------</p>
  
  <pre><code>@style nVarchar(150) = ''
  Select * FROM mytable WHERE style LIKE '%' + @style + '%' AND ...
  </code></pre>
  
  <p>With LIKE clause I can use wildcard like <code>'%'</code> in case style does not have any value but I cannot search couple value in the same column.<br>
  So, keep in mind I'm using "Stored Procedure" since this is kind of complex and I need to use this in couple places. I will have have 10-12 query for this search.<br>
  Any Idea will help me to get this going. </p>