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 ««1234»»»

Conditional Statements in WHERE Clauses Expand / Collapse
Author
Message
Posted Monday, July 26, 2004 11:57 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, September 6, 2005 10:53 AM
Points: 107, Visits: 1

Thank you very much; every day I find I know very little about sql (Mainly a C/C++ dude).

Hey RonKyle, could you post an example of the join that you're talking about so we can talk about how it would use the indices?  It looks like that might be the cleanest solution yet.

Thanks!




Post #128247
Posted Friday, August 20, 2004 6:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 2, 2005 4:16 AM
Points: 8, Visits: 1

Hi all,

Did RonKyle post a sample of his solution as I have a little task which could use his technique.

If not perhaps he could post a sample so we may see how it works and give some feedback.

Regards Steve

Post #133070
Posted Tuesday, October 26, 2004 11:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 7, 2005 1:14 PM
Points: 3, Visits: 1
My first ever post. Here are my 2 cents:
  1. Sotiris' article has some good acdemic interest.  But just as laperrej pointed out, we can't use anything like that in production because of performance issues.
  2. AKM's answer is not a bad one, for a large, busy system. I would actually push it futher: use a master stored procedure to call sub stored procedures. Yes, it would be lots of typing. But performance is always the key.
  3. Dynamic construct a query is not a bad idea. When you use EXEC sp_executesql @CMD, the plan is most likely cached and reused. Comparing with executing a wrong plan, the time consumed to generate a plan is neglectable
  4. If I am tasked to write such a query, I would do this:
CREATE PROC usp_GetComstomers
@CompY VARCHAR(100)='%',
@ContY VARCHAR(100)='%',
@CompN VARCHAR(100)='NoNothingNoneWhateverString',
@ContN VARCHAR(100)='NoNothingNoneWhateverString'
AS
/*
Example:
EXEC usp_GetComstomers '%','Germany','A%'
EXEC usp_GetComstomers '%the%'
*/
SET NOCOUNT ON
SELECT *
FROM dbo.Customers
WHERE CompanyName LIKE @CompY
 AND Country LIKE @ContY
 AND CompanyName NOT LIKE @CompN
 AND Country NOT LIKE @ContN
This would be fast and not too bad looking.



Post #143585
Posted Friday, September 28, 2007 12:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 2,570, Visits: 2,429
In this case, I prefer dynamic sql and add only conditions I need. Performance are granted more than complex and useless conditions. :)
Post #403903
Posted Friday, September 28, 2007 2:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 1, 2013 10:41 AM
Points: 19, Visits: 51
How about:...?

set @companyname = 'A%'
set @companynameOp = 'ne'
set @country = 'Germany'
set @countryOp = 'eq'

set@companyName =
case @companyNameOp
when '' then null
when 'eq' then -- Operator is "equals"
@companyName
when 'bg' then -- Operator is "begins with"
@companyName +'%'
when 'ed' then -- Operator is "ends with"
'%' + @companyName
when 'ct' then -- Operator is "contains"
'%' + @companyName + '%'

set@country =
case @countryOp
when '' then null
when 'eq' then -- Operator is "equals"
@country
when 'bg' then -- Operator is "begins with"
@country +'%'
when 'ed' then -- Operator is "ends with"
'%' + @country
when 'ct' then -- Operator is "contains"
'%' + @country + '%'

-- Ok, now let's form our query.

select
customerid, companyname, country
from
customers
where
(companyName like @companyName or @companyName is null)
AND
(country like @country or @country is null)
Post #403922
Posted Friday, September 28, 2007 3:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
I'm with Adam, check out MVP Erland Sommarskog's article on this: http://www.sommarskog.se/dyn-search.html

However, for the example presented by the article, can someone tell me what's wrong with simply this?...

select
customerid, companyname, country
from
customers
where
(companyname LIKE @companyname) AND (country LIKE @country)


This is able to deal with the different operators (NOT LIKE 'A%' can be dealt with by using '[0-9, b-z]%'), and can use indexes if they're there (i.e. avoid a table scan).



Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #403940
Posted Friday, September 28, 2007 3:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 1, 2013 10:41 AM
Points: 19, Visits: 51
RyanRandall (9/28/2007)

However, for the example presented by the article, can someone tell me what's wrong with simply this?...
select
customerid, companyname, country
from
customers
where
(companyname LIKE @companyname) AND (country LIKE @country)


This is able to deal with the different operators (NOT LIKE 'A%' can be dealt with by using '[0-9, b-z]%'), and can use indexes if they're there (i.e. avoid a table scan).


Exacxtly, though if you throw an 'or @companyName is null' in there the optimiser will ignore any 'parameters' that are not set. eg,

(companyName like @companyName or @companyName is null)
AND
(country like @country or @country is null)

Post #403941
Posted Friday, September 28, 2007 3:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
Ben (9/28/2007)


Exacxtly, though if you throw an 'or @companyName is null' in there the optimiser will ignore any 'parameters' that are not set.


Yes, I know about that (it's in Erland's article), and it is the best method. I left it out here to keep the example aligned with the article and keep the point clear.



Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #403946
Posted Friday, September 28, 2007 5:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 21, 2008 6:51 AM
Points: 3, Visits: 19
Sorry but I couldn't see the whole example. The scroll bars on the example did not work.

Steve
Post #403969
Posted Friday, September 28, 2007 5:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
Steve Coleman (9/28/2007)
Sorry but I couldn't see the whole example. The scroll bars on the example did not work.



Steve


Steve,

It looks like this...


-- Declare some local variables. Actually, we are creating a pair of variables
-- for each column included in our WHERE clause.
-- The first variable represents the value we are filtering and the second
-- represents the "operator" for the filter.

declare @companyName varchar(255)
declare @companyNameOp varchar(2)
declare @country varchar(255)
declare @countryOp varchar(2)

-- Let's set some sample values now. The values you see here represent the second
-- of the two scenarios described above, i.e. all records for companies located in Germany,
-- excluding companies starting with the letter A

-- Operators are defined here with arbitrary, two-letter values.
-- Of course you could define your own set of operators, with different
-- naming conventions. For our example, here's the meaning of each possible
-- value:

-- ne = not equal
-- eq = equal
-- bg = begins with
-- ed = ends with
-- ct = contains

-- For our example, we are using only varchar fields in our WHERE clause.
-- It is very easy, though, to define operators for other data types as well.


set @companyname = 'A%'
set @companynameOp = 'ne'
set @country = 'Germany'
set @countryOp = 'eq'



-- Ok, now let's form our query.

select
customerid, companyname, country
from
customers
where
case @companyNameOp
when '' then 1 -- Operator not defined, get everything
when 'eq' then -- Operator is "equals"
case when companyname like @companyName then 1 else 0 end
when 'bg' then -- Operator is "begins with"
case when companyname like @companyName +'%' then 1 else 0 end
when 'ed' then -- Operator is "ends with"
case when companyname like '%' + @companyName then 1 else 0 end
when 'ct' then -- Operator is "contains"
case when companyname like '%' + @companyName +'%' then
1 else 0 end
when 'ne' then -- Operator is "not equal"
case when companyname not like @companyName then 1 else 0 end end =1

AND

-- Same approach for the second field

case @countryOp
when '' then 1
when 'eq' then
case when country like @country then 1 else 0 end
when 'bg' then
case when country like @country +'%' then 1 else 0 end
when 'ed' then
case when country like '%' + @country then 1 else 0 end
when 'ct' then
case when country like '%' + @country +'%' then 1 else 0 end
when 'ne' then
case when country not like @country then 1 else 0 end
end =1



Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.
Post #403971
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse