March 14, 2006 at 4:50 am
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)
March 14, 2006 at 7:40 am
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
March 14, 2006 at 7:52 am
thanks for the info, I'll check it out
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply