Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old June 22nd, 2003, 02:04 PM
Authorized User
 
Join Date: Jun 2003
Location: , , Yugoslavia.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default Case in Where Clause

Hi everybody
Can I use case statement in WHERE clause.
For example

DECLARE @VAR1 nvarchar(50)
DECLARE @VAR2 nvarchar(50)
SELECT * FROM ORDERS
WHERE CASE @VAR1
          WHEN 'Customers' THEN CustomerID = @VAR2
          WHEN 'Employee' THEN EmployeeID = @VAR2
          END

This is not working OK.
Can someone explain me the right way?
Thanks.
Best regards
Alex
Reply With Quote
  #2 (permalink)  
Old June 22nd, 2003, 04:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

In your situation, where you wish to select on two entirely different columns depending on a parameter, it is probably better to use an IF/ELSE construction, as:
Code:
IF @Var1='Customers' THEN
   SELECT * FROM Orders WHERE CustomerID=@Var2;
ELSE
   SELECT * FROM Orders WHERE EmployeeID=@Var2;
It's important to realize that CASE is an expression and not an executable statement.

The general form of the predicate of a WHERE clause is:
Code:
 expression operator expression
where operator is = or <> or > or < etc. The WHERE clause in your query does not follow this form, which is why you were having problems.

If you insist on using the CASE expression, then you can try a couple of ugly alternatives, like:
Code:
   SELECT * FROM Orders
    WHERE CustomerID=CASE @Var1 WHEN 'Customers' THEN @Var2 ELSE 0 END
       OR EmployeeID=CASE @Var1 WHEN 'Employees' THEN @Var2 ELSE 0 END
assuming that 0 is not a legal value for either 'CustomerID' or 'EmployeeID'. You could also try a nested CASE like:
Code:
   SELECT * FROM Orders
    WHERE 1 = 
      CASE @Var1
         WHEN 'Customers' THEN CASE WHEN CustomerID=@Var2 THEN 1 ELSE 0 END
         WHEN 'Employees' THEN CASE WHEN EmployeeID=@Var2 THEN 1 ELSE 0 END
      END
I think that the CASE expression doesn't work well in this situation and that the IF/ELSE is simpler and easier to understand.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
The Following User Says Thank You to Jeff Mason For This Useful Post:
  #3 (permalink)  
Old June 23rd, 2003, 08:31 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Yugoslavia.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Very Much
Alex
Reply With Quote
  #4 (permalink)  
Old August 6th, 2004, 02:43 PM
Registered User
 
Join Date: Aug 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think this should work for you...

SELECT * FROM ORDERS
WHERE
(
  CASE
    WHEN @VAR1 = 'Customers' AND CustomerID = @VAR2 THEN 1
    WHEN @VAR1 = 'Employee' AND EmployeeID = @VAR2 THEN 1
    ELSE 0
  END
) = 1
Reply With Quote
  #5 (permalink)  
Old September 18th, 2008, 08:57 AM
Registered User
 
Join Date: Sep 2008
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

or you could do this ...

SELECT *
FROM ORDERS
WHERE (@VAR1 = 'Customers' and CustomerID = @VAR2) OR
      (@VAR1 = 'Employee' and EmployeeID = @VAR2)

Reply With Quote
  #6 (permalink)  
Old September 18th, 2008, 02:56 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Jeff's first answer is best (though it doesn't handle an illegal value for @VAR1) and SBacon's answer is a close second, but Jeff's other answers and Craig's answer are all overkill.

Why not just:
Code:
DECLARE @VAR1 nvarchar(50)
DECLARE @VAR2 nvarchar(50)
SELECT * FROM ORDERS
WHERE @VAR2 = ( CASE @VAR1
                WHEN 'Customers' THEN CustomerID 
                WHEN 'Employee'  THEN EmployeeID 
                ELSE -1
                END )


The ELSE is in there to protect against an illegal value for @VAR1 of course.
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
  #7 (permalink)  
Old September 18th, 2008, 03:31 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by Old Pedant
 ...Jeff's other answers ... are all overkill.
Well... I did say they were "...a couple of ugly alternatives..." ;)

(Any idea why we're responding to a 5 year old original post? :D )

Jeff Mason
je.mason@comcast.net
Reply With Quote
  #8 (permalink)  
Old September 18th, 2008, 03:50 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

> (Any idea why we're responding to a 5 year old original post?

Ummm...because I inanely didn't look at the original message date and because SBacon is a complete newbie and how the heck did he *find* this thread in the first place and... Well, maybe Google sent him this way??? But then why is he responding instead of asking? And... Ehhh, leave it at "because I inanely...".
Reply With Quote
  #9 (permalink)  
Old September 18th, 2008, 03:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by Old Pedant
 > And...  Ehhh, leave it at "because I inanely...".
:D

Jeff Mason
je.mason@comcast.net
Reply With Quote
  #10 (permalink)  
Old August 4th, 2011, 05:57 PM
Registered User
 
Join Date: Apr 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default For everyone else

3 years later...
Thanks for posting no matter how old the O.P. is! My google search for this exact problem brought me here and you guys just saved me 8 hours of work! Old Pedant, you almost nailed it. The big reason to respond to any post is...

Other People can use your answers.

Please don't not post. (ugh, that sounded weird)
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Case Statement In Where Clause Logic dwj119 SQL Server 2000 4 October 27th, 2011 03:14 PM
CASE WHEN WHERE CLAUSE deontae45 SQL Server 2000 2 January 11th, 2011 09:03 AM
Case in Where Clause demiwolf SQL Server 2000 3 March 23rd, 2006 11:45 AM
case clause in query mateenmohd SQL Server 2000 2 February 22nd, 2005 04:49 AM
case clause in Access mateenmohd Access 19 May 6th, 2004 03:54 AM



All times are GMT -4. The time now is 10:04 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.