Multiple condition based on where clause in sql server 2008

• SQL006

  SSCertifiable

  Points: 5071

  More actions

  March 25, 2013 at 7:46 am

  #270370

  I had a table Product with these fields

  ProductName,

  Description,

  Manufacturer,

  Product Code,

  Technology,

  Address,

  Country,

  City,

  Length

  I want to search all these fields with these criteria

  SearchType='Contains'

  SearchType='Ends'

  SearchType='Equals'

  SearchType='Greater'

  SearchType='Less'

  SearchType='Not Equal'

  SearchType='Like'

  Another parameter i will pass whether to use AND/OR operation

  At a time we can search 6 fields with any of the search criteria and AND\OR operation

  the condition in where clause will be like this

  WHERE (ProductName = @ProductName) AND (Country Like @Country +%) OR (Manufacturer Likre(Ends) '%' + @Manufacturer ) AND (Length < (Greater) @Length)

  So the user can input 6 conditions based on the above criteria.

  Can u help to get the logic for where clause

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  March 25, 2013 at 7:49 am

  #1600318

  http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

  You'll need dynamic SQL on this one.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• SQL006

  SSCertifiable

  Points: 5071

  More actions

  March 25, 2013 at 11:34 pm

  #1600610

  GilaMonster (3/25/2013)

  ---

  http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

  You'll need dynamic SQL on this one.

  Thanks gail for the reply..let me go through the article

  I am planning to pass the condition as a table value parameter, each row has a different condition

  Chk the attached picture below

  I am attaching the search diagram where i have to use this procedure