CASE in WHERE clause

microsoft.public.sqlserver.programming

7 Dec 2006 4:13 AM

shank

You can probably figure out what I'm trying to accomplish by loking at the
below better than I could explain. I'm getting syntax errors. Can someone
lend some insight?

DECLARE @A varchar(255), @T varchar(255), @BT int, @BA int, @SBF int, @MS
varchar(5)
SET @A = 'A'
SET @BT = '1'
SET @MS = '2'

IF @BT = 1 OR @BA = 1
SELECT LeadVoc, Media, Price, Title, Artist
FROM Songs

WHERE (Artist LIKE @A + '%') AND (Title LIKE @T + '%') AND
CASE @MS
WHEN '2' Then (Media LIKE 'MP3' + '%')
WHEN '3' Then (Media LIKE 'MP4' + '%')
WHEN '4' Then (Media LIKE 'MP4' + '%') AND (LeadVoc LIKE 'Yes')
WHEN '5' Then (Media LIKE 'MP4' + '%') AND (LeadVoc LIKE 'No')
ELSE '%' END

thanks