Multiple condition based on where clause in sql server 2008

  • 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

  • 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
  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply