Need HELP with using IF or CASE in Where clause..

• crabjoe

  SSC-Addicted

  Points: 468

  More actions

  April 10, 2014 at 9:35 am

  #146869

  Basically, I'm trying to do the below...

  ==============================

  DECLARE @period VARCHAR(10)

  SET @period = 'current'

  select * from t_rbt_data

  where dt between '2014-01-01' and '2014-03-31'

  AND toy_type = 'robot'

  IF @period = 'current' THEN

  AND material = 'metal'

  END

  ==============================

  I'm trying to add a clause to the Where statement, when @period = 'current' if it's anything else, I don't want to add another clause...

  HELP!! How do I do this using a CASE or IF???

  THANKS!!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 10, 2014 at 10:21 am

  #1704971

  There's a bunch of ways. Do be aware that this often degrades performance, so rather avoid on larger tables or important queries

  select * from t_rbt_data

  where dt between '2014-01-01' and '2014-03-31'

  AND toy_type = 'robot'

  AND (material = 'metal' OR @period != 'current')

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 10, 2014 at 10:25 am

  #1704973

  Try this:

  DECLARE @period VARCHAR(10)

  SET @period = 'current'

  select * from t_rbt_data

  where

  dt >= '2014-01-01' and

  dt < '2014-04-01' -- this ensures you get all records within the specified time frame regardless of time

  AND toy_type = 'robot'

  AND (@period <> 'current' or (@period = 'current' and material = 'metal'))