Solved

operand case statement in a where clause, advanced search

Posted on 2007-03-26
3
536 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Email Header Detail 12 51
Querying data from 3 SQL tables 2 30
Help creating a spatial object in SQL Server 4 20
optimize stored procedure 6 23
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

813 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now