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
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: 380
I am converting embedded SQL (in Java code) to a stored procedure.
They are building SQL WHERE clause dynamically in JAVA.
What is the best way to implement this java logic in SQL?


sqlStmt = "SELECT * FROM
Imports..tdsix_balance B
LEFT JOIN Imports..tdsix_account A ON B.ACID = A.ACID
LEFT JOIN (SELECT DISTINCT ACID, MAX(AsOf) AS ASOF FROM Imports..tdsix_account GROUP BY ACID) Z
ON A.ACID = Z.ACID"
" +
"where ";

if (account.equals("")){
sqlStmt = sqlStmt +
"(B.ACID like '5F%' or B.ACID like '5G%' or B.ACID like '5H%' or B.ACID like '5T%'" +
" or B.ACID like '5J%' or B.ACID like '01%' or B.ACID like '03%')" +
"and B.asof =? " +
"and (A.asof = Z.ASOF or A.asof is null) " +
"order by B.acid";
}
else {
sqlStmt = sqlStmt +
"B.ACID like '" + account + "%' " +
"and B.asof =? " +
"and (A.asof = Z.ASOF or A.asof is null) " ;




To me it looks like I have to use CASE in WHERE clause but I don't know how to write all those "LIKE ... OR LIKE ..."
Or maybe I should forget about CASE and just use IF:

IF @account IS NULL
BEGIN
SELECT * FROM
Imports..tdsix_balance B
LEFT JOIN Imports..tdsix_account A ON B.ACID = A.ACID
LEFT JOIN (SELECT DISTINCT ACID, MAX(AsOf) AS ASOF FROM Imports..tdsix_account GROUP BY ACID) Z
ON A.ACID = Z.ACID
WHERE
LEFT(B.ACID,2) IN ('5F','5G','5H','5T','5J','01','03')
AND(A.AsOf = Z.ASOF or A.AsOf IS NULL)
ORDER BY
B.ACID
END
ELSE
BEGIN
SELECT * FROM
Imports..tdsix_balance B
LEFT JOIN Imports..tdsix_account A ON B.ACID = A.ACID
LEFT JOIN (SELECT DISTINCT ACID, MAX(AsOf) AS ASOF FROM Imports..tdsix_account GROUP BY ACID) Z
ON A.ACID = Z.ACID
WHERE
B.ACID LIKE @account+'%'
AND (A.AsOf = Z.ASOF OR A.AsOf IS NULL)
ORDER BY
B.ACID
END


Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 1721
Personally, if the where clause is that complex I like the idea of just using an IF statement like in your second example if for no other reason that it is more readable and 2 years from now it will be easier for you or someone else to figure out what it's doing.

On the other hand, I don't use CASE statements in the WHERE clause at all anymore. I use something like this:



AND ((@Status = 1 AND table.Status = 1)
OR
(@Status = 0))
AND ((@ProductColor = 'yellow' AND table.Color = 'yellow')
OR
(@ProductColor = 'red' AND table.Color = 'red')
OR
(@ProductColor IN ('blue','green','')))



Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16588 Visits: 17024
This is a type of catch-all query. Gail's blog has a great way of handling this type of thing.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
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: 380
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


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16588 Visits: 17024
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 code
IF @account = '' OR LEN(@account) = 0 OR @account IS NULL
WHERE Id1 BETWEEN 'AA' AND 'KK' AND Id2 IS NULL
ELSEIF @account <> '' AND LEN(@account) > 0 AND @account IS NOT NULL
WHERE Id1 BETWEEN 'MM' AND 'ZZ' AND Id2 IS NOT NULL
ELSEIF @account > 100
WHERE ....... and so on



IF statements control the flow of processing. You can't use them like that in a where clause.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16588 Visits: 17024
You will have to suffer with performance issues because of the inability to properly use execution plans with this type of thing unless you can use the dynamic sql approach that Gail suggests.

Something like this.


WHERE
(
(@account = '' OR LEN(@account) = 0 OR @account IS NULL)
AND
(
(Id1 BETWEEN 'AA' AND 'KK' AND Id2 IS NULL)
OR
(Id1 BETWEEN 'MM' AND 'ZZ' AND Id2 IS NOT NULL)
)
)
OR
(
@account > 100
)



The big issue here is that it difficult to decipher the logic. Not too bad when you write it but in 6 months when you have to debug it or the next person comes along it is painful.

Surprising your boss is ok with pass through sql and not dynamic parameterized sql. Many people don't like dynamic sql because they don't understand how to properly protect yourself using parameters.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
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: 3042 Visits: 5478

Gail's blog recommends using dynamic SQL
but our boss doesn't like it at all.


Is your boss SQL Server professional or just an idiot with a lot of ambitions?
For what you're trying to achieve, dynamic SQL is the best option!
So just tell your boss, if he wants good code, you better to follow the right direction, not the one you boss like or dislike. Sounds like kindergarten to me!

_____________________________________________
"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
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: 380
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.
brad.mason5
brad.mason5
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1522 Visits: 2010
In this case I would have both the Dynamic SQL stored proc and non dynamic stored proc.

Compare both the execution plans. Also checking statistics (SET STATISTICS IO ON) and clearing before executing each stored proc.

It seems your manager really wants assurance and was burnt in the past. I would add some a @debug paramter to the dynamic stored proc. If @debug=1 then print the statement and do not execute. This way it makes it easier for a dev to see what is going on. I use this all the time if I am doing dynamic SQL.

If you see huge gains by using dynamic sql and allow for easy debugging your boss might be happy. It is worth a try. I think you will be surprised at the results.
brad.mason5
brad.mason5
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1522 Visits: 2010
Forgot to include cache

Also checking statistics (SET STATISTICS IO ON) and clearing cache [b][/b]before executing each stored proc.
--Do not run in PROD!!!

DBCC FREEPROCCACHE
DBBC DROPCLEANBUFFERS
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