Using CASE in SQL Server 2008

Question

I am having trouble using CASE when attempting to return the appropriate results based on whether a certain value is passed.

For example I would like to run a query if the suburb is passed

WHEN @suburb <> '' THEN
@suburb 
END
= [Suburb]
AND 
[City] = @city

However how do I only run the query so that when no suburb is passed it only uses the city?

Should I rather write the query as follows?

WHERE
(
    [Suburb] = @suburb
    AND
    [City] = @city
)
OR
(
    [City] = @city
)

@Aaron Bertrand - Why would not recommend writing it in the second from? — PeanutsMonkey, Feb 21 '12 at 22:26
@Aaron Bertrand - I did think about that after I had posted it however is the logic inappropriate apart from the repetition? — PeanutsMonkey, Feb 21 '12 at 22:37
No, but there are probably dozens of ways you could write the query and get the right results. That doesn't make all of them appropriate. Don't just think about correct logic or even performance, think about readability too. — Aaron Bertrand, Feb 21 '12 at 22:38
@Aaron Bertrand - May be a n00b question. How do you know whether it is logical if the results you are getting is what is required? — PeanutsMonkey, Feb 22 '12 at 00:28
I didn't say your query wasn't logical. I simply recommended not writing it that way because it is redundant. Follow the DRY principle when you can (don't repeat yourself). — Aaron Bertrand, Feb 22 '12 at 00:33

score 1 · Accepted Answer · answered Feb 21 '12 at 21:21

1

You don't really need CASE:

WHERE (suburb = @suburb OR @suburb = '' OR @suburb IS NULL) AND city = @city

answered Feb 21 '12 at 21:21

John Pick

5,562
31
31

That worked like a charm. A question though as I a newbie to SQL. I am guessing that the OR statement is only fulfilled if the first condition fails e.g. if `suburb = @suburb` does not have a value only the does it then proceed to process `@suburb = ''`. Is thar right? – PeanutsMonkey Feb 21 '12 at 21:32
1

No, SQL does not short-circuit. All expressions are evaluated. – John Pick Feb 21 '12 at 21:36
The article at http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx seems to suggest it does unless I have misunderstood. If all conditions are evaluated, is it posisble that all those conditions can be met? – PeanutsMonkey Feb 21 '12 at 21:48
I'm not sure where that article implies that short-circuiting is guaranteed, but it is not, not even inside CASE. Please read this entire question and its answers: http://dba.stackexchange.com/questions/12941/does-sql-server-read-all-of-a-coalesce-function-even-if-the-first-argument-is-no/12945#12945 – Aaron Bertrand Feb 21 '12 at 21:59
I stand corrected. SQL does not *reliably* short-circuit. Good discussion here: http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated – John Pick Feb 21 '12 at 22:00
@Aaron Bertrand & John Pick - Thanks guys. – PeanutsMonkey Feb 21 '12 at 22:26
@John Pick I would even state it the other way around, sort of. *SQL Server may short circuit, but don't rely on it.* – Aaron Bertrand Feb 21 '12 at 22:36
@John Pick - When do you use `CASE`? – PeanutsMonkey Feb 23 '12 at 19:35
@PeanutsMonkey MSDN has some good examples: http://msdn.microsoft.com/en-us/library/ms181765.aspx – John Pick Feb 23 '12 at 19:48

score 1 · Answer 2 · answered Feb 21 '12 at 21:21

Since you're using SQL Server 2008, you'll want to use the OPTION (RECOMPILE) hint here to avoid caching an inappropriate plan. See Catch-all queries for more details.

...
WHERE ((@suburb = '') OR (@suburb <> '' AND [Suburb] = @suburb))
    AND [City] = @city
OPTION (RECOMPILE)

score 1 · Answer 3 · answered Feb 21 '12 at 21:23

1

There are a few alternative ways to achieve, you can try one of these..

WHERE [City] = @city
AND ([Suburb] = @suburb  OR '' = '')

answered Feb 21 '12 at 21:23

Thit Lwin Oo

3,388
3
20
23

score 0 · Answer 4 · answered May 16 '13 at 05:44

0

SELECT FirstName, LastName, TelephoneNumber, "When to Contact" = 
 CASE
      WHEN TelephoneSpecialInstructions IS NULL THEN 'Any time'
      ELSE TelephoneSpecialInstructions
 END

FROM Person.vAdditionalContactInfo;

answered May 16 '13 at 05:44

Santosh wavare

19
1

Using CASE in SQL Server 2008

4 Answers4