sql server programming: 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

You're using CASE like IF. It doesn't work that way. CASE is an expression
that returns a single result. You can't say CASE WHEN something THEN go do
something else END.

The best way to get the best answer is to provide DDL, sample data, and
desired results. Then you don't have to worry about explaining (except when
you provide the most basic case and leave out the 20 exceptions you also
need to handle).
http://www.aspfaq.com/5006

Maybe this will work, but I'm not exactly sure because you have one CASE
that really represents 2, and I'm not sure whether your fallback ('%')
applies to Media or LeadVoc or both. I also don't see where @BT is ever
used, and if @A or @T could be NULL, you may want to use SET @A =
COALESCE(@A, '') and SET @T = COALESCE(@T, '').


SELECT
LeadVoc, Media, Price, Title, Artist
FROM
Songs
WHERE
Artist LIKE @A + '%'
AND Title LIKE @T + '%' AND
Media LIKE CASE
WHEN @ms = '2' THEN 'MP3%'
WHEN @ms IN ('3','4','5') THEN 'MP4%'
ELSE '%'
END
AND LeadVoc = CASE @ms
WHEN '4' THEN 'Yes'
WHEN '5' THEN 'No'
ELSE LeadVoc
END;





[quoted text, click to view]