CODEWITHSUNDEEP
t-sqlsql-server-2012
Colin Hodges
Colin Hodges

Reputation: 25

How does this case statement work?

I'm not clear on what is happening with this case statement in a where clause. Setup:

CREATE TABLE [dbo].[Cars](
    [Car] [varchar](20) NULL,
    [Fast] [varchar](10) NULL
) ON [PRIMARY]

INSERT [dbo].[Cars] ([Car], [Fast]) VALUES (N'Ford', N'No')
INSERT [dbo].[Cars] ([Car], [Fast]) VALUES (N'Holden', N'Yes')
INSERT [dbo].[Cars] ([Car], [Fast]) VALUES (N'Mazda', N'Not Really')
INSERT [dbo].[Cars] ([Car], [Fast]) VALUES (N'Toyota', N'Yes')

and the query:

declare @Include_List varchar(100) = (select 'Get fast cars, Get slow cars')

SELECT Car,Fast
FROM    Cars
where   case 
        when Fast = 'Yes' and CHARINDEX('Get fast cars',@Include_List) > 0 then 1
        when Fast = 'No' and CHARINDEX('Get slow cars',@Include_List) > 0 then 1
        else 0
    end = 1

I'm expecting the first when expression to match for the fast cars and then drop out of the case statement. however the second when expression is also tested and matches for the slow cars. The result set contains fast and slow cars.

Should it not be that the result for the first equality check is returned only ?

Upvotes: 1

Views: 281

Answers (1)

tezzo
tezzo

Reputation: 11115

The SELECT tests every record of Cars.

  • The fast cars match the first WHEN in the CASE block
  • The slow cars match the second WHEN in the CASE block
  • The not really fast doesn't match any criteria in the CASE block

If you want only fast cars try to declare @Include_list like this:

DECLARE @Include_List VARCHAR(100) = (SELECT 'Get fast cars')

Upvotes: 1

Related Questions