Conditional WHERE Clause

• southwynd

  Old Hand

  Points: 315

  More actions

  April 15, 2009 at 10:41 am

  #203594

  I have a query I am attempting to work through. There is a hierarchy of IDs in my table (ADM, RAD, and OE). In my stored procedure I pass in values for these IDs that could be NULL. Below are the requirements and a sample table. Can any one out there help?

  --@ADM and ADM must match. Will not be NULL.

  -- If RAD is not null, @RAD and RAD must match. Otherwise both must be null to match.

  -- If OE is not null @OE and OE must match. Otherwise both must be null to match.

  -- If there is not a match ommit the record.

  -- So :

  -- # = #

  -- NULL = NULL

  -- # <> NULL

  DECLARE @sample TABLE

  (

  ADM VARCHAR(20),

  RAD VARCHAR(20),

  OE VARCHAR(20)

  )

  INSERT INTO @sample

  SELECT '2252295195',

  '001567427',

  NULL

  UNION ALL

  SELECT '2252295195',

  NULL,

  NULL

  UNION ALL

  SELECT '2252295196',

  '001567427',

  NULL

  DECLARE @ADM VARCHAR(20)

  DECLARE @RAD VARCHAR(20)

  DECLARE @OE VARCHAR(20)

  SET @ADM = '2252295195'

  SET @RAD = '001567427'

  --SET @RAD = NULL

  SET @OE = '14211787.001'

  --SET @OE = NULL ;

  SELECT *

  FROM @sample

  WHERE --Can you help me here?

• Mando2

  SSChasing Mays

  Points: 609

  More actions

  April 15, 2009 at 11:41 am

  #978284

  SELECT *

  FROM @sample s

  WHERE s.ADM = @adm

  AND ((s.RAD IS NULL AND @RAD IS NULL) OR (s.RAD = @RAD))

  AND ((s.OE IS NULL AND @oe IS NULL) OR (s.OE = @oe))

• southwynd

  Old Hand

  Points: 315

  More actions

  April 15, 2009 at 11:49 am

  #978289

  Perfect! Thanks so much.