How to use IF/ELSE in a SELECT - SQL SERVER

Question

I am trying to remove the "s" from the word "years" when the COUNT() is < 2 but my syntax is not right for some reason:

Errors: Incorrect syntax near the keyword 'IF'. Incorrect syntax near the keyword 'convert'.

stuff(
        (
        select ',' + Related_name + ' (' + (select
        IF COUNT(begin_date) > 1 BEGIN convert(varchar(10), COUNT(begin_date))  + ' years)' END
        ELSE BEGIN convert(varchar(10), COUNT(begin_date))  + ' year)'
        from cus_relationship subInnerR
        where subInnerR.master_customer_id = c.master_customer_id
        and subInnerR.related_master_customer_id = innerR.related_master_customer_id
        and subInnerR.relationship_type = 'ADVSPR'
        and subInnerR.relationship_code = 'CLUB'
        and subInnerR.reciprocal_code = 'FACADV')
        from cus_relationship innerR
        where [...]

You can better use case instead of if.... – Rahul Tripathi Sep 18 '13 at 14:46 — Rahul Tripathi, Sep 18 '13 at 14:46

Rahul Tripathi · Accepted Answer · 2013-09-18T15:39:52.677

Try like this(As commented by gvee in comments as this reduces some repeated code!!):-

 select ',' + Related_name + ' (' + (select
    Convert(varchar(10), Count(begin_date)) + ' year' + 
    CASE WHEN Count(begin_date) > 1 THEN 's'    ELSE '' END + ')'
    from cus_relationship subInnerR
    where subInnerR.master_customer_id = c.master_customer_id
    and subInnerR.related_master_customer_id = innerR.related_master_customer_id
    and subInnerR.relationship_type = 'ADVSPR'
    and subInnerR.relationship_code = 'CLUB'
    and subInnerR.reciprocal_code = 'FACADV')
    from cus_relationship innerR
    where [...]

You forgot the `+ ')'` after the `CASE` statement =D – Code Maverick Sep 18 '13 at 15:15 — Code Maverick, Sep 18 '13 at 15:15

score 1 · Answer 2 · edited May 23 '17 at 12:30

1

Maybe this helps

In tsql you use CASE instead of IF

edited May 23 '17 at 12:30

Community

1
1

answered Sep 18 '13 at 14:47

mhafellner

458
3
9

score 1 · Answer 3 · answered Sep 18 '13 at 14:51

1

I am not a fan of reusing the same code, so I'd use CASE like this:

CONVERT(VARCHAR(10), COUNT(begin_date)) 
+ ' year' 
+ CASE WHEN COUNT(begin_date) > 1 THEN 's' ELSE '' END
+ ')'

split out on multiple lines for readability

answered Sep 18 '13 at 14:51

Code Maverick

20,171
12
62
114

score 0 · Answer 4 · answered Sep 18 '13 at 14:46

0

You'll need to do this with a CASE statement instead of IF:

case
    when COUNT(begin_date) > 1 then
        convert(varchar(10), COUNT(begin_date))  + ' years)'
    else
        convert(varchar(10), COUNT(begin_date))  + ' year)'
end

answered Sep 18 '13 at 14:46

Joe Enos

39,478
11
80
136

1

Quick thought to reduce repeated code: `Convert(varchar(10), Count(begin_date)) + ' year' + CASE WHEN Count(begin_date) > 1 THEN 's' ELSE '' END` – gvee Sep 18 '13 at 14:50

How to use IF/ELSE in a SELECT - SQL SERVER

4 Answers4