CODEWITHSUNDEEP
sqlt-sqlcasewhere-clause
Chris
Chris

Reputation: 7611

T-SQL: CASE statement in WHERE?

I have the following WHERE clause in my SQL query currently:

WHERE c.Town LIKE '%' + @Town + '%'

What I want do is make the first '%' optional - so if the user has selected to just filter by records that just start with the given text, the WHERE would be

WHERE c.Town LIKE @Town + '%'

I assumed the simplest way to do this would be CASE statements, but I'm having no joy as it seems the syntax is incorrect. Can I get any pointers? Here's the CASE statement I was trying:

WHERE c.Town LIKE (CASE WHEN @FilterOptions = 1 THEN '%' ELSE '') + @Town + '%'

Upvotes: 0

Views: 3162

Answers (3)

Ratz
Ratz

Reputation: 1

Case should have end.

I think the below statement can help you.

WHERE c.Town LIKE (CASE WHEN @FilterOptions = 1 THEN '%' ELSE '' END) + @Town + '%

Upvotes: 0

AakashM
AakashM

Reputation: 63338

A CASE must end with an END. Try

WHERE c.Town LIKE (CASE WHEN @FilterOptions = 1 THEN '%' ELSE '' END) + @Town + '%'

Upvotes: 1

Petar Ivanov
Petar Ivanov

Reputation: 93030

You missed the END:

WHERE c.Town LIKE (CASE WHEN @FilterOptions = 1 THEN '%' ELSE '' END) + @Town + '%'

Upvotes: 5

Related Questions