Checking null values?

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  March 14, 2006 at 4:50 am

  #167334

  can someone please help me with updating a table that has null values in some of fields? I've used the following peice of dynamic sql in a stored procedure and this works fine

  set @ssql = 'update [TI_Work] set ['+ @sEin +']' + ' =  '''+ @sArea +'''  where [date1] between  ''' + @sDateFrom + '''  and  ''' + @sDateTo + ''' '

  exec (@ssql)

  the problem I have is some of the fields have a null value in them, these I don't want to update, the SQL I've tried below doesn't work, can anyone help?

  set @ssql = 'update [TI_Work] set ['+ @sEin +']' + ' =  '''+ @sArea +'''  where [date1] between  ''' + @sDateFrom + '''  and  ''' + @sDateTo + ''' and   ['+ @sEin +']'  + is  not isnull'

  exec (@ssql)

   

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  March 14, 2006 at 7:40 am

  #626501

  It should work. In your example I found 2 errors.

  Missing apostrophe before the is in the where clause and is not isnull is not correct syntax should just be just NULL

  set @ssql = 'update [TI_Work] set ['+ @sEin +']' + ' =  '''+ @sArea +'''  where [date1] between  ''' + @sDateFrom + '''  and  ''' + @sDateTo + ''' and   ['+ @sEin +']'  + is  not isnull'

  Slightly modified example. Search for Handling Nulls on this site, there have been several articles written about handling nulls relatively recently

  create table #t1_Work (pk int identity, Field1 varchar(10), Field2 varchar(10))

  insert into #t1_Work (Field1)

  select 'foo'

  insert into #t1_Work (Field2)

  select 'Bar'

  select * from #t1_Work

  declare @ssql nvarchar(1000)

  ,    @sEin varchar(10)

  ,    @sArea varchar(10)

  set @sEin = 'Field1'

  set @sArea = 'FooBar'

  set @ssql = 'update [#T1_Work] set ['+ @sEin +']' + ' =  '''+ @sArea +'''  where   ['+ @sEin +']'  + ' is  not null'

  print @ssql

  exec (@ssql)

  select * from #T1_Work

  Results

  1 FooBar NULL

  2 NULL Bar

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  March 14, 2006 at 7:52 am

  #626506

  thanks for the info, I'll check it out