CODEWITHSUNDEEP
sqlsql-servert-sql
Nathan
Nathan

Reputation: 13

How to build Case statement using multiple if statements?

I have a checkbox list on my website. I pass the value of the checkbox to @State. The below code works if just one checkbox is checked but does not work when multiple checkboxes are checked.

I have tried using case statements but have been unsuccessful in building it I get errors such as "incorrect syntax near the keyword 'update'

Select CASE WHEN @State LIKE '%All Areas%' then Update Users set FilterState = 'Michigan, Texas, Louisiana, Arkansas, Oklahoma, Kansas, Missouri, South Dakota, Nebraska, Iowa, Wisconsin, Ohio, Montana, Wyoming, North Dakota, Minnesota, District Of Columbia, Delaware' where Email = '[User:Email]'
Else
END
From Users

IF (@State LIKE '%All Areas%')<br>
BEGIN<br>
Update Users set FilterState = 'Michigan, Texas, Louisiana, Arkansas, Oklahoma, Kansas, Missouri, South Dakota, Nebraska, Iowa, Wisconsin, Ohio, Montana, Wyoming, North Dakota, Minnesota, District Of Columbia, Delaware' where Email = '[User:Email]'<br>
END<br>
IF(@State LIKE '%A%')<br>
BEGIN<br>
Update Users set FilterState = 'Michigan' where Email = '[User:Email]'
END<br>
IF(@State LIKE '%B%')<br>
BEGIN<br>
Update Users set FilterState = 'Texas, Louisiana, Arkansas, Oklahoma, Kansas, Missouri' where Email = '[User:Email]'
END<br>
IF(@State LIKE '%C%')<br>
BEGIN<br>
Update Users set FilterState = 'South Dakota, Nebraska, Iowa, Wisconsin' where Email = '[User:Email]'<br>
END<br>
IF(@State LIKE '%D%')<br>
BEGIN<br>
Update Users set FilterState = 'Ohio' where Email = '[User:Email]'
END<br>
IF(@State LIKE '%E%')<br>
BEGIN<br>
Update Users set FilterState = 'Montana, Wyoming, North Dakota, Minnesota' where Email = '[User:Email]'<br>
END<br>

If the user checks A and D then the result needs to put the two together.

Upvotes: 0

Views: 59

Answers (1)

George Dando
George Dando

Reputation: 444

If you're after the concatenation of several answers, you could use += to append to the previous updates:

IF(@State LIKE '%D%')
BEGIN
Update Users set FilterState += 'Ohio' where Email = '[User:Email]'
END

To avoid multiple updates to the table, if you're building up a string I'd do the string concatenation in a variable beforehand, followed by the update, something like:

DECLARE @filterState VARCHAR(200)
SET @filterState = CASE WHEN @State LIKE '%A%' THEN 'Michigan' ELSE '' END
                 + CASE WHEN @State LIKE '%B%' THEN 'Texas, Louisiana, Arkansas, Oklahoma, Kansas, Missouri' ELSE '' END
                --etc

Update Users 
set FilterState = @filterState
where Email = '[User:Email]'

Upvotes: 1

Related Questions