operand case statement in a where clause, advanced search
Posted on 2007-03-26
I have an advanced search form where I want a user to be able to choose their operand (>, <, >=, <=, =) for the rating of a tutorial (1,2,3,4,5). The operand and the rating int get passed into the stored proc. What I want to do is use a case statement in the where clause that gives them the operand they chose without having to write multiple sql statement. As a simple example (tried in query analyzer) I'm trying to do the following but am getting an error saying "Incorrect syntax near '>'. Can someone explain what I'm doing wrong. Thanks.
DECLARE @what varchar(5)
SET @what = '>'
select tutorial_id, tutorial_title, tutorial_description
avgrating = (CASE when @what = '>' then > else = END) @avgrating
order by tutorial_id