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

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
mmedi005Asked:
Who is Participating?
 
Ken ButtersCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
tim_csCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Disregard my comment.  The first two will be much easier to implement.
0
 
mmedi005Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.