problem with nulls

• Raghunath Garlapati

  Ten Centuries

  Points: 1122

  More actions

  August 18, 2009 at 11:58 pm

  #138303

  Hi,

  I have a problem with coalesce on nullabe columns ,

  I have a procdure which will accept a procedure for filter data say @filterdata,

  in the procedure i have a select statement where i need to filte for data with @filterdata if not null

  so for that what i did is where mucolumn=coleasce(@filterdata,mycolumn) but now the problm is mycolumn is a nullable column when we pass null for @filterdata it is not returning any records where it has to retrieve all the records, please help me to solve this.

  /********************************************************
  Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
  ********************************************************/

• arun.sas

  SSChampion

  Points: 11831

  More actions

  August 19, 2009 at 2:06 am

  #1041320

  hi,

  For better assistance, post you procedure

• winash

  SSChampion

  Points: 11446

  More actions

  August 19, 2009 at 5:50 am

  #1041417

  It looks like you are trying to compare NULL values - and you cannot equate two NULL values...

  I've put some sample code below - let me know if this is similar to what you are talking about:

  DECLARE @tab TABLE(Val1 int, Val2 int);

  DECLARE @filterdata int

  --setup data

  INSERT @tab(Val1,Val2)

  SELECT CASE WHEN number%3 = 0 THEN NULL ELSE number END,number

  FROM master.dbo.spt_values WHERE

  name IS NULL

  AND number http://www.sqlservercentral.com/articles/Advanced+Querying/2829/