Solved

operand case statement in a where clause, advanced search

Posted on 2007-03-26
3
540 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
SQL Job Hung 17 37
Change this SQL to get all nodes 3 41
Checking for column changes SQL 2014 4 24
sql server major issue  need help 2 53
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

730 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