dynamic where with boolean

• ZA_Crafty

  Ten Centuries

  Points: 1228

  More actions

  June 25, 2009 at 4:06 am

  #207471

  With this, @SomeID is dynamic. you can either pass an ID, or null to return all records. But the boolean will only select true records.

  Declare @SomeID int

  Declare @SomeBool bit

  Set @SomeID = 5

  Set @SomeBool = 1

  Select

  mT.*

  from

  myTable mT (Nolock)

  Where

  (isnull(@SomeID,0) = 0 or SomeID = @SomeID)

  And(SomeBool = @SomeBool)

  But this is what I want. I cant use isnull(@SomeBool,0) because that will parse as false.

  Declare @SomeID int

  Declare @SomeBool bit

  Set @SomeID = 5

  Set @SomeBool = null

  Select

  mT.*

  from

  myTable mT (Nolock)

  Where

  (isnull(@SomeID,0) = 0 or SomeID = @SomeID)

  And(@SomeBool???????????????????????????????) --What comes here????

• arun.sas

  SSChampion

  Points: 11831

  More actions

  June 25, 2009 at 4:16 am

  #1015179

  ZA_Crafty (6/25/2009)

  ---

  you can either pass an ID, or null to return all records. But the boolean will only select true records.

  Hi,

  try this

  where

  SomeID = case when @SomeID is not null then @SomeID else SomeID end

  and SomeBool = case when @SomeBool is not null then @SomeBool else SomeBool end

  ARUN SAS