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 NULLwhereStmt = "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 on googles mail service