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 12»»

CASE in WHERE clause Expand / Collapse
Author
Message
Posted Tuesday, March 12, 2013 11:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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

Post #1430015
Posted Tuesday, March 12, 2013 2:24 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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','')))


Post #1430093
Posted Tuesday, March 12, 2013 2:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 13,095, Visits: 11,928
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)
Post #1430113
Posted Wednesday, March 13, 2013 7:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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

Post #1430392
Posted Wednesday, March 13, 2013 8:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 13,095, Visits: 11,928
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)
Post #1430402
Posted Wednesday, March 13, 2013 8:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 13,095, Visits: 11,928
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)
Post #1430412
Posted Wednesday, March 13, 2013 9:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062

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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1430476
Posted Wednesday, March 13, 2013 8:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:35 PM
Points: 83, Visits: 201
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.
Post #1430712
Posted Wednesday, March 13, 2013 9:02 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 961, Visits: 932
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.

Post #1430717
Posted Wednesday, March 13, 2013 9:07 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 961, Visits: 932
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
Post #1430718
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse