Case Statement in SQL Query

问题:

I was trying to use case statement in the select statement like this in SQL server 2005 and i get the error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." Cant a Case be used inside SQL query?!

declare @TypeofDayID int
set @TypeofDayID = (Select TypeofDayID from RepInfo where RepInfoID = @RepInfoID)


Select CASE 
    WHEN @TypeofDayID = 1 
        THEN (Select * from RepInfo RD inner join SellingInfo S on S.RepInfoID = @RepInfoID)
    WHEN @TypeofDayID = 2
        THEN (Select * from RepInfo RD inner join UpgradingInfo U on U.RepInfoID = @RepDailyID)
    WHEN @TypeofDayID = 9 or @TypeofDayID = 10
        THEN (Select * from RepInfo RD inner join DeliveryInfo D on D.RepDailyID = @RepDailyID)
    END
from RepInfo RD

回答1:

CASE isn't used for logical flow control... use IF / ELSE IF instead:

declare @TypeofDayID int
set @TypeofDayID = (Select TypeofDayID from RepInfo where RepInfoID = @RepInfoID)

IF @TypeofDayID = 1 
  Select * 
  from RepInfo RD inner join SellingInfo S on S.RepInfoID = @RepInfoID
ELSE IF @TypeofDayID = 2
  Select * 
  from RepInfo RD inner join UpgradingInfo U on U.RepInfoID = @RepDailyID
ELSE IF @TypeofDayID = 9 or @TypeofDayID = 10
  Select * 
  from RepInfo RD inner join DeliveryInfo D on D.RepDailyID = @RepDailyID

Keep in mind... since you're using SELECT *, and joining to a different table based on @TypeOfDayID, you'll likely end up with a jagged result set, meaning that you'll have a varying number of columns based on which branch is taken.

This can be a pain to work with programmatically, so it would be a good idea to avoid SELECT * for this reason, as well as other reasons...

回答2:

I think what you're trying to do can be better handled this way.

declare @TypeofDayID int
set @TypeofDayID = (Select TypeofDayID from RepInfo where RepInfoID = @RepInfoID)

IF @TypeofDayID = 1 
    Select * 
    from RepInfo RD 
    inner join SellingInfo S on S.RepInfoID = @RepInfoID
ELSE IF @TypeofDayID = 2
    Select * 
    from RepInfo RD 
    inner join UpgradingInfo U on U.RepInfoID = @RepDailyID
ELSE IF @TypeofDayID = 9 or @TypeofDayID = 10
    Select * 
    from RepInfo RD 
    inner join DeliveryInfo D on D.RepDailyID = @RepDailyID