Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

operand case statement in a where clause, advanced search

Posted on 2007-03-26
3
Medium Priority
?
554 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 750 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Suggested Courses

578 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