sql Case statement bring back multiple values

Question

I want to bring back the county Id and the county name. How can I fix this query?

DECLARE @test int = 0;


select 
CASE (@test)
    when 0 then (SELECT co.Id, co.Description
                 FROM Dictionary.Counties as co
                 INNER JOIN CountyCollaboration as cc on cc.CountyId = co.Id
                 WHERE cc.CollaborationId = (SELECT cc1.CollaborationId from CountyCollaboration as cc1
                                             WHERE cc1.CountyId = 34))
END

I get the error only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

If I comment out co.Description so I'm only bringing back co.Id, I get a different error: subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >=, or when the subquery is used as as expression.

If you can explain why you want to do this there may be a simple workaround which would avoid any CASE or IF statements. — bendataclear
– bendataclear, Commented Oct 30, 2013 at 15:56

Phil Sandler · Accepted Answer · 2013-10-30 15:54:10Z

1

You can only return one expression from a CASE statement. Try using IF/ELSE instead.

The CASE statement in T-SQL is not a control flow statement like CASE/SWITCH in many programming languages.

answered Oct 30, 2013 at 15:54

Phil Sandler

28.1k21 gold badges88 silver badges153 bronze badges

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

2 Comments

Damien_The_Unbeliever Over a year ago

In fact, there is no CASE statement in T-SQL. There's a CASE expression, which in common with other expressions computes a value.

user1202606 Over a year ago

this is exactly what I was looking for, thanks Phil. As soon as it lets me I will accept your answer, I have to wait a couple more minutes.

Dan Bracuk · Accepted Answer · 2013-10-30 15:58:23Z

1

I suggest restructuring your query to look like this.

select id, country 
from 
(select co.id
, co.country
, case @test  code for test goes here end caseresult
from all that stuff you have as a subquery in your question
) derivedtable

answered Oct 30, 2013 at 15:58

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Comments

D Stanley · Accepted Answer · 2013-10-30 15:58:54Z

0

Your subquery was returning multiple fields and possible multiple records. A subquery like this must always return one field and one record.

I'm guessing the INNER JOIN is causing multiple records to be returned. If all of the records have the same value you can to a DISTINCT or TOP 1.

answered Oct 30, 2013 at 15:58

D Stanley

153k12 gold badges190 silver badges257 bronze badges

Collectives™ on Stack Overflow

sql Case statement bring back multiple values

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related