Using case when - Is this possible?

Question

Right off the bat - I'm quite new to 'case when'. I read the following: How do I perform an IF...THEN in an SQL SELECT? however it didn't really answer my question.

Essentially what I'm trying to do is something along the lines of the following:

select
    section_name, *
from
    property.lease_period lp
where
    lp.lease_current_stop_date < getdate() and (lp.lease_status = 'Active' or lp.lease_status = 'Overholding')
    and lp.period_id = @period_id
    and lp.building_id = @building_id
    and not exists
    (
        select 1
        from lease_deal.lease
        where lp.suite_name = tenancy_reference
        and lp.building_id = building_id
    )
    case when(@section_name <> 'ALL')
    then(and upper(section_name) = upper(@section_name))
    end
order by period_id desc

Is this possible? If so what am I doing wrong?

Tl;dr:

Essentially I would like:

and upper(section_name) = upper(@section_name)

To only apply to my where clause when @section_name is not equal to 'ALL'

Do not ever use select *. Here you would be returning section_name twice which is wasteful of server resources. Same thing happens if you have a join. Plus select * by itself is slower. Use only the columns you actually need. — HLGEM, Dec 20 '11 at 14:35
I agree however this SQL is being used for an SSRS report - My normal point of call there is to start off with selecting all columns and then change the query to be more specific once I know everything that the report will be using (clients often request additional data to be added throughout the development process). I do concede that I should have used lp.* here though - declaring * itself is bad practice. — Michael A, Dec 20 '11 at 23:42

score 3 · Accepted Answer · answered Dec 20 '11 at 05:07

3

You just can change your (non-working) CASE to

AND (@section_name = 'ALL' OR upper(section_name) = upper(@section_name))

answered Dec 20 '11 at 05:07

Oleg Dok

21,109
4
45
54

2

And if your server is not CASE-censitive - you can just skip the UPPER functions to gain more performance and to use (may be) proper index – Oleg Dok Dec 20 '11 at 05:10

score 1 · Answer 2 · edited Dec 20 '11 at 05:47

1

This can be done in a simpler way without the need to use CASE. It will be something like this:

and ((upper(section_name) = upper(@section_name) and @section_name <> 'ALL') OR @section_name ='ALL')

edited Dec 20 '11 at 05:47

T.Rob

31,522
9
59
103

answered Dec 20 '11 at 05:19

mrayess

41
1
6

Excellent! For interests sake is there a way to accomplish what I outlined above with a case? – Michael A Dec 20 '11 at 05:24

score 0 · Answer 3 · edited Dec 20 '11 at 06:46

0

AND upper(section_name)=CASE WHEN @section_name <> 'ALL' THEN upper(@section_name)
                                                         ELSE upper(section_name)
                        END

edited Dec 20 '11 at 06:46

Igor Borisenko

3,806
3
34
49

answered Dec 20 '11 at 05:18

Zohaib

7,026
3
26
35

Using case when - Is this possible?

3 Answers3