Solved

operand case statement in a where clause, advanced search

Posted on 2007-03-26
3
538 Views
Last Modified: 2010-03-20
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 @what

select tutorial_id, tutorial_title, tutorial_description
from uw_toots
where
avgrating = (CASE when @what = '>' then > else = END) @avgrating
order by tutorial_id

0
Comment
Question by:jrmcintosh
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18796638
select tutorial_id, tutorial_title, tutorial_description
from uw_toots
where (avgrating => @avgrating and @what = '>' ) or (avgrating = @avgrating and @what = '=' )
order by tutorial_id
0
 
LVL 9

Expert Comment

by:dbeneit
ID: 18796641
You can't use a operator in case.
try
DECLARE @what varchar(5)
SET @what = '>'

select @what

select tutorial_id, tutorial_title, tutorial_description
from uw_toots
where
(CASE when @what = '>' then avgrating = > @avgrating else avgrating = @avgrating END)
order by tutorial_id
0
 
LVL 10

Accepted Solution

by:
lahousden earned 250 total points
ID: 18797397
Like dbeneit says, you can't use CASE for this - this should work, however:

select tutorial_id, tutorial_title, tutorial_description
from uw_toots
where ('>' = @what and avgrating > @avgrating)
or  ('<' = @what and avgrating < @avgrating)
or  ('>=' = @what and avgrating >= @avgrating)
or  ('<=' = @what and avgrating <= @avgrating)
or  ('=' = @what and avgrating = @avgrating)
order by tutorial_id
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question