Filter records with store procedure

• lior gal

  SSC Journeyman

  Points: 94

  More actions

  September 11, 2004 at 2:15 pm

  #86375

  HI

  I need a store procedure to filter the records that I return from the DB but i dont want to write a Different sql statement for each condition

  for example if i want to filter by 2 variables I DONT WANT THE SP BODY TO BE:

  if(not var1 is null)

  select...from.. where field1=var1

  if(not var2 is null)

  select...from.. where field2=var2

  instead I want to insert the if else into the where part of the sql, something like:

  select.. from.. if (not is null var1) where field1=var1...

  Is it possible ???

  I Have something that almost work:

  select...from...

  WHERE (Field1= CASE WHEN @Var1 IS NULL  THEN Field1  ELSE @Var1 END) 

  but it dosent work if field1 is null, in this case instead of getting all the records I Get none.

   

• Adam Machanic

  SSCoach

  Points: 15370

  More actions

  September 13, 2004 at 9:23 am

  #522779

  This article should help you: http://www.sommarskog.se/dyn-search.html

  --
  Adam Machanic
  whoisactive

• lior gal

  SSC Journeyman

  Points: 94

  More actions

  September 14, 2004 at 6:14 am

  #522936

  Thanks alot it was very helpful.