SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Conditional Statements in WHERE Clauses


Conditional Statements in WHERE Clauses

Author
Message
thormj
thormj
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 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!





Beedles About
Beedles About
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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


Gary Xu
Gary Xu
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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. :-)




Carlo Romagnano
Carlo Romagnano
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9352 Visits: 3441
In this case, I prefer dynamic sql and add only conditions I need. Performance are granted more than complex and useless conditions. Smile
Ben-425648
Ben-425648
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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)
RyanRandall
RyanRandall
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6099 Visits: 4652
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.
Ben-425648
Ben-425648
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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)


RyanRandall
RyanRandall
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6099 Visits: 4652
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.
Steve Coleman
Steve Coleman
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 19
Sorry but I couldn't see the whole example. The scroll bars on the example did not work.



Steve
RyanRandall
RyanRandall
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6099 Visits: 4652
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search