Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple condition based on where clause in sql server 2008 Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 7:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 26, 2014 1:59 AM
Points: 181, Visits: 987
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
Post #1434917
Posted Monday, March 25, 2013 7:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:54 PM
Points: 42,434, Visits: 35,488
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 2008, MVP
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

Post #1434921
Posted Monday, March 25, 2013 11:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 26, 2014 1:59 AM
Points: 181, Visits: 987
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


  Post Attachments 
filter.JPG (4 views, 40.90 KB)
Post #1435266
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse