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
– Rahul Tripathi

2013-09-18 14:46:23 +00:00
Commented Sep 18, 2013 at 14:46 — Rahul Tripathi
– Rahul Tripathi, Commented Sep 18, 2013 at 14:46

Rahul Tripathi · Accepted Answer · 2013-09-18 15:39:52Z

2

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 [...]

edited Sep 18, 2013 at 15:39

answered Sep 18, 2013 at 14:47

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Code Maverick Over a year ago

You forgot the + ')' after the CASE statement =D

Community · Accepted Answer · 2017-05-23 12:30:12Z

1

Maybe this helps

In tsql you use CASE instead of IF

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Sep 18, 2013 at 14:47

mhafellner

4683 silver badges9 bronze badges

Comments

Code Maverick · Accepted Answer · 2013-09-18 14:51:13Z

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, 2013 at 14:51

Code Maverick

20.5k12 gold badges65 silver badges117 bronze badges

Comments

Joe Enos · Accepted Answer · 2013-09-18 14:46:19Z

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, 2013 at 14:46

Joe Enos

40.6k11 gold badges86 silver badges144 bronze badges

1 Comment

gvee Over a year ago

Quick thought to reduce repeated code: Convert(varchar(10), Count(begin_date)) + ' year' + CASE WHEN Count(begin_date) > 1 THEN 's' ELSE '' END

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related