Trying to add CASE with WHERE

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

A where clause must be constructed in the form of an expression.

so

Where p.SellerProductID not like '%-Wav%'

So you cannot have the entire portion of your expression within a single then statement of your case.

it would only work this way

Where p.SellerProductID not like case when @AlbumType = 'mp3' then '%AL%' when then ... end

LInks to read up on

you may end up having to do dynamic sql.

dba321 Hall of Fame Points: 3917 More actions · Answer 2

this is one reason I hate SQL. we have to come up with crazy ass ways to get it to do what we want cause it's not an OOP language. AHHHHH!!!!

ok, I have to break...this is giving me a headache. I also tried this with no luck

WHERE

CASE WHEN @AlbumType = 'mp3' THEN P.SellerStoreID = @SellerStoreID END

AND

CASE WHEN @AlbumType = 'mp3' THEN P.SellerProductID not like '%AL%' END

AND

CASE WHEN @AlbumType = 'mp3' THEN P.SellerProductID not like '%-WAV%' END

AND

CASE WHEN @AlbumType <> 'mp3' THEN P.SellerStoreID = @SellerStoreID END

AND

CASE WHEN @AlbumType <> 'mp3' THEN P.SellerProductID like '%-WAV%' END

dba321 Hall of Fame Points: 3917 More actions · Answer 3

here, someone else found a way for it to work

FROM

[dbo]

.[Product] P WITH (NOLOCK)

WHERE

P.SellerStoreID = @SellerStoreID

AND

((@AlbumType = 'mp3' and P.SellerProductID not like '%AL%' and P.SellerProductID not like '%-WAV%')

OR

(@AlbumType <> 'mp3' and P.SellerProductID like '%-WAV%'))