CASE along with IN

• SqlServerPass

  SSCommitted

  Points: 1709

  More actions

  December 19, 2011 at 2:18 am

  #248254

  How to achieve something like this

  SELECT * FROM MyTable WHERE

  CASE WHEN 1=1 THEN ID END IN case when 1=1 then (1,2,3,4,5) end

  Appreciating your help.

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  December 19, 2011 at 2:37 am

  #1422993

  CASE resolves a value or column:

  SELECT aValue = CASE WHEN 1 = 1 THEN 10 ELSE 20 END

  Your query fails because CASE is resolving a statement.

  Try this:

  ;WITH MyTable AS (SELECT TOP 10 ID = ROW_NUMBER() OVER(ORDER BY [Name]) FROM sys.columns)

  SELECT *

  FROM MyTable

  WHERE (1=1 AND ID IN (1,2,3,4,5))

  OR (0=1 AND ID = ID)

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]