Null column values in select query equality check

• Senthil T

  Hall of Fame

  Points: 3367

  More actions

  January 17, 2007 at 11:52 am

  #176477

  Hi,

  I have this sproc, @age parameter is passed in to this sproc and it returns all the rows from the Users table that matches with the age parameter. @age is an optional parameter, so if the parameter is null, all the rows are to be returned.

  SELECT name, age FROM users WHERE age = ISNULL(@age, age)

  It works fine when an integer value is passed, i.e @age is not null. But when @age is null, the query does not return all the rows from the table. It returns only the rows which has some value for the age column. All the rows where age is NULL is not returned.

  Is there an easy solution to deal with this? There are quite a few other parameters in this sproc and most of them can be null. Any suggestions?

  Thanks.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 17, 2007 at 11:55 am

  #683819

  try SELECT name, age FROM users WHERE ISNULL(age,0) = ISNULL(@age, 0)

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  January 17, 2007 at 2:09 pm

  #683857

  WHERE (age = @age OR @age IS NULL)

  _____________
  Code for TallyGenerator

• Arthur-398534

  Valued Member

  Points: 59

  More actions

  January 17, 2007 at 5:54 pm

  #683888

  Hi,

  I would suggest to stay out of the Three-State-Logic with NULL's involved.

  This could be very complicated when working with complex database solutions.

  A simple idea could be;

  Create Procedure s_ProductTest

  @parm1 int = 9999999,

  @parm2 int = 9999999

  As

  Begin

   Select * from Producttest Where (product = @Parm1 or @Parm1 = 9999999)

  End

  exec s_productTest 123

   

   

  Cheers

  Arthur