passing multiple parameters to a stored proc

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  April 3, 2009 at 8:38 am

  #971619

  Hi Bob

  Very nice solution with the string-bitmap! 😉

  @ J-F

  Nice approach over an external file!

  My bad, I did not use that for a long time, it got me saying stupid stuff again![/b]

  There is nothing stupid you said.

  Greets

  Flo

• JacekO

  SSCertifiable

  Points: 6610

  More actions

  April 3, 2009 at 9:15 am

  #971657

  Florian Reischl (4/2/2009)

  ---

  David,

  What about this:

  SELECT *

  FROM mytab

  WHERE col1 = ISNULL(@col1, col1)

  AND col2 = ISNULL(@col2, col2)

  AND col3 = ISNULL(@col3, col3)

  AND col4 = ISNULL(@col4, col4)

  AND col5 = ISNULL(@col5, col5)

  It's a little less than one million lines.

  Greets

  Flo

  Flo.

  This is not going to work correctly if the fields have NULLs.

  Regardless of the ANSI_NULLS settings. Any rows with NULLs on searched columns will be skipped.

  So if you have NULLable fields it is better to use syntax proposed by John

  EDIT

  Added John's code

  select*

  fromTable_1

  where (@select_1 is null or col_1 = @select_1)

  and (@select_2 is null or col_2 = @select_2)

  and (@select_3 is null or col_3 = @select_3)

  and (@select_4 is null or col_4 = @select_4)

  ---------------------------------------------
  [font="Verdana"]Nothing is impossible.
  It is just a matter of time and money.[/font]

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  April 3, 2009 at 9:34 am

  #971685

  JacekO (4/3/2009)

  ---

  Florian Reischl (4/2/2009)

  ---

  David,

  What about this:

  SELECT *

  FROM mytab

  WHERE col1 = ISNULL(@col1, col1)

  AND col2 = ISNULL(@col2, col2)

  AND col3 = ISNULL(@col3, col3)

  AND col4 = ISNULL(@col4, col4)

  AND col5 = ISNULL(@col5, col5)

  Flo.

  This is not going to work correctly if the fields have NULLs.

  Regardless of the ANSI_NULLS settings. Any rows with NULLs on searched columns will be skipped.

  So if you have NULLable fields it is better to use syntax proposed by John

  Thanks for correction! You are right!

  Greets

  Flo

• JacekO

  SSCertifiable

  Points: 6610

  More actions

  April 3, 2009 at 9:42 am

  #971694

  No problem.

  Been there, got burnt... 😉

  The biggest surprise was that this is ANSI_NULLs independent. Regardless how you have this option set. ON or OFF the NULLs are not equal. I guess ISNULL must be doing something internally...

  ---------------------------------------------
  [font="Verdana"]Nothing is impossible.
  It is just a matter of time and money.[/font]