SQL Help

I'm having a bit of a problem with the CASE statement in my code below. Any ideas why it's not working.

Basically I have SSRS report that has a drop down of all 50 states. The report returns all the Contacts and Firms in the chosen State. I was asked to also add Northern California to the list of states.

So my goal is to say that if it's a normal state, then simply return all contacts in that State. If user chose Northern California, return contacts only in zip codes 93000 - 96000.
Select C.FirstName as 'FirstName', C.LastName as 'LastName', F.FirmName as 'Firm', C.Email
From Contacts C JOIN Firms F ON C.FirmNo = F.FirmNo
JOIN State S ON S.StateNo = F.StateNo
Where C.NoMarketing = 0
AND C.Email != ''
AND CASE @State WHEN @State like 'California - Northern Cali' THEN F.PostCode BETWEEN 93000 AND 96000
		ELSE S.StateName = @State
END


Select S.StateName as 'StateName'
From State S
Where S.[Disabled] = 0
AND CountryNo = 295
AND StateNo NOT IN (159,162,160,161)
UNION
Select 'California - Northern Cali'
From State S
Where S.[Disabled]=0

Open in new window

InfoTechEEAsked:
Who is Participating?
 
lluddenCommented:
Select C.FirstName as 'FirstName', C.LastName as 'LastName', F.FirmName as 'Firm', C.Email
From Contacts C JOIN Firms F ON C.FirmNo = F.FirmNo
JOIN State S ON S.StateNo = F.StateNo
Where C.NoMarketing = 0
AND C.Email != ''
AND ((@State = 'California - Northern Cali' AND F.PostCode BETWEEN 93000 AND 96000) OR
            (S.StateName = @State))

0
 
InfoTechEEAuthor Commented:
Getting a weird error on exectution...

An error occurred during local report processing.
Cannot read the next data row for the dataset RB8.
Conversion failed when converting the varchar value '91521-4583' to data type int.

So I just changed the above code to

AND ((@State = 'California - Northern Cali' AND F.PostCode BETWEEN '93000' AND '96000') OR
            (S.StateName = @State))

Is that OK?
0
 
lluddenCommented:
I forgot the quotes, so good catch.  That should do it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.