Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CASE in WHERE clause


CASE in WHERE clause

Author
Message
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478
RVO (3/13/2013)
My boss is a professional. He was a developer recently.
I saw some of his stored procedures - good code.
I think he has a point. A few years ago at one large bank
production support team had big problems debugging complex long stored procedures
with lots of dynamic SQL. Production Support Team Lead just begged us to avoid using too much dynamic SQL.
Some people argue that for dynamic SQL, SQL Server engine cannot build optimized execution plan.
Not sure who's right who's wrong.


I'm sure. Your boss and Production Support Team are wrong on the following:
"for dynamic SQL, SQL Server engine cannot build optimized execution plan"

Now, you can write crap code in any form, dynamic or non-dynamic.
For the issue you are solving, properly written dynamic SQL will outperform any other solution. If you try, you will find it yourself! For dynamic-SQL optimizer will find and cach the best possible plans for each of SQL variation which will be built. If you use IF or CASE WHEN based solution, optimizer will not be able to come up with effective plan for any of the cases. Why? Gail Shaw explained it very well in her blog. If you have a doubt - just check it yourself.

Please remember, that there were many people around who argued that the Earth is flat, as if it would be round, we would fall down from it. http://en.wikipedia.org/wiki/Flat_Earth_Society
;-)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8296
RVO (3/13/2013)
Thanks all for your response.

Gail's blog recommends using dynamic SQL
but our boss doesn't like it at all.
I have to learn how to handle it either with CASE
or IF. My problem with IF was when there are
a lot of conditions IF becomes messy so I try to find out
if I can write logic in WHERE clause more accurate, easy to understand.
I'm talking about something like this:

Pseudo Java:
IF @account = '' OR LEN(@account) = 0 OR @account IS NULL
whereStmt = "WHERE Id1 BETWEEN 'AA' AND 'KK' AND Id2 IS NULL"
ELSEIF @account <> '' AND LEN(@account) > 0 AND @account IS NOT NULL
whereStmt = "WHERE Id1 BETWEEN 'MM' AND 'ZZ' AND Id2 IS NOT NULL"
ELSEIF @account > 100
whereStmt = "WHERE ......." and so on



You will either use dynamic sql to do this work or you will have to code a bajillion permutations of actual tsql that is hit with a massive sequence of IF statements testing the parameters or you will suffer HORRIBLY BAD PERFORMANCE AND CONCURRENCY. There is absolutely no other option. From my quick review I will say that dynamic sql is by far the best solution here. Tell your boss that I have gotten FIVE ORDERS OF MAGNITUDE performance improvement from scenarios like this by using dynamic sql. And concurrency goes WAY up when you stop doing table scans and hash joins that will result from the IS NULL OR construct. Guard against SQL Injection and you are good to go.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
RVO
RVO
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 373
;-)Thanks a lot for your response.
I always feel a very friendly supportive environment here.

I will definetly compare performance of regular vs dynamic SQL procedures.
But later.

My goal now is to come up with a "pure TSQL" mechanism
that handles this dynamic WHERE clause.
I see it all over the place in the Java code I am converting.
So it's like a pattern that I need find out how to handle nicely.
I feel like if I give up and do it with dynamic SQL - I surrender
and don't improve my TSQL skills .. :-)

Again, a pattern is (java code)

sqlStmt = "select * from IndustryMap";
sqlStmt += " WHERE 1=1 " + whereSQLStmt;
      if(cusip != null && cusip.length()>0){
         whereSQLStmt += " and id_cusip = '" + cusip + "' ";}
      
      if(ticker != null && ticker.length()>0){
         whereSQLStmt += " and ticker = '" + ticker + "' ";}
      
      if(exchangeCode != null && exchangeCode.length()>0){
         whereSQLStmt += " and exch_Code = '" + exchangeCode + "' ";



They dynamically build WHERE clause
if input parameter is not null.
There are hundreds of these cases in Java.
RVO
RVO
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 373
I tried this
select * from IndustryMap
WHERE 1 =1
AND id_cusip = COALESCE(@cusip,id_cusip)
AND ticker = COALESCE(@ticker,ticker)
AND exch_code = COALESCE(@exchangeCode,exch_code)



and was ready to scream Hurray but realized that
when any of the columns have a null value
this won't work. Because "AND col = NULL" is not valid.
Keep searching.... :-)
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8296
RVO (3/14/2013)
I tried this
select * from IndustryMap
WHERE 1 =1
AND id_cusip = COALESCE(@cusip,id_cusip)
AND ticker = COALESCE(@ticker,ticker)
AND exch_code = COALESCE(@exchangeCode,exch_code)



and was ready to scream Hurray but realized that
when any of the columns have a null value
this won't work. Because "AND col = NULL" is not valid.
Keep searching.... :-)


That will get you HORRIBLE plans too for several reasons.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5935 Visits: 8296
Note, this isn't just about the WHERE clause. You can also potentially eliminate complete hits (joins) on tables if you don't actually need to filter on them based on input parameters.

I am wondering - why are you looking to get rid of a java process that already creates the proper (assuming it does) dynamic SQL?

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3040 Visits: 5478
Keep searching.... Hehe
I also like to invent bicycles in my free time. Stupid clients/employers, they don't like to pay me for spending time on such an exciting hobby of mine ... ;-)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
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