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

x
?
Solved

How can I use the key word IN inside a WHERE clause follewed by a CASE

Posted on 2012-08-20
6
Medium Priority
?
492 Views
Last Modified: 2012-08-20
I'm trying to achieve this:

	SELECT BookTypeID, 
		     Description
    FROM BookTypes
	WHERE CASE @BookID
			WHEN 1 THEN BookTypeID IN ( 1, 2, 3, 7, 8, 9, 10 )
			WHEN 2 THEN BookTypeID IN ( 1, 2, 7, 8, 9, 10 )
			WHEN 9 THEN BookTypeID IN ( 1, 2, 3, 7, 8, 9, 10 )
			ELSE BookTypeID IN ( 1, 2, 7, 10 )
		  END
	ORDER BY Description;

Open in new window


I get this error:

Incorrect syntax near the keyword 'IN'.

what do I do to achieve what I'm trying to do?

thanks
0
Comment
Question by:mmedi005
6 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 38313411
SELECT BookTypeID,
     Description
FROM BookTypes
WHERE ( @BookID = 1 and BookTypeID IN ( 1, 2, 3, 7, 8, 9, 10 ) ) OR
      ( @BookID = 2 and  BookTypeID IN ( 1, 2, 7, 8, 9, 10 )
      ( @BookID = 9 and  BookTypeID IN ( 1, 2, 3, 7, 8, 9, 10 )) OR
      ( @BookID not in (1,2,9) and BookTypeID IN ( 1, 2, 7, 10 ) )
ORDER BY Description;
0
 
LVL 15

Assisted Solution

by:tim_cs
tim_cs earned 400 total points
ID: 38313415
You can't do that.  You will have to do something like this.

WHERE
   (@BookID = 1 AND BookTypeID IN (1,2,3,7,8,9,10))
   OR (@BookID = 2 AND BookTypeID IN (1,2,7,8,9,10))
   OR (@BookID = 9 AND BookTypeID IN ( 1, 2, 3, 7, 8, 9, 10 ))
   OR (@BookID NOT IN (1,2,9) AND BookTypeID IN (1, 2, 7, 10 ))
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38313427
afaik there's no way to pull off what you are trying, so you'd have to split this up into separate queries like this...

IF @BookID = 1
   SELECT BookTypeID, Description
   FROM BookTypes
   WHERE BookTypeID IN ( 1, 2, 3, 7, 8, 9, 10 )
   ORDER BY Description
ELSE IF @BookID = 2
   SELECT BookTypeID, Description
   FROM BookTypes
   WHERE BookTypeID IN ( 1, 2, 7, 8, 9, 10 )
   ORDER BY Description
ELSE IF @BookID = 9
   SELECT BookTypeID, Description
   FROM BookTypes
   WHERE BookTypeID IN ( 1, 2, 3, 7, 8, 9, 10 )
   ORDER BY Description
ELSE
   SELECT BookTypeID, Description
   FROM BookTypes
   WHERE BookTypeID IN ( 1, 2, 7, 10 )
   ORDER BY Description
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Accepted Solution

by:
Ken Butters earned 1200 total points
ID: 38313437
Looks to me that in your final result set... you are selecting bookTypeID of 1,2,7,10 always.... so those should be removed from your other clauses...

leaving this:

SELECT BookTypeID,
    Description
    FROM BookTypes
    Where (bookID = 1 and BookTypeID IN( 3, 8, 9 )) or
          (bookID = 2 and BookTypeID IN ( 8, 9 )) or
          (bookID = 9 and BookTypeID IN ( 3, 8, 9 )) or
          BookTypeID in (1,2,7,10)
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38313446
Disregard my comment.  The first two will be much easier to implement.
0
 

Author Closing Comment

by:mmedi005
ID: 38313466
thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

572 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