--try
if exists(select 1 from sysobjects where xtype='p' and name='update_null')
drop proc update_null
go
/*
功能:处理表test字段中的NULL值,用''替换
作者:WGS
创建时间:2006-06-14
调用事例:
1。对一个表操作:update_null 'tb1'
2。对整个数据库操作:sp_msforeachtable "update_null '?'"
*/
create proc update_null(@tbname nvarchar(50))
as
declare @fld varchar(20),@ftype varchar(30),@fid int,@sql varchar(300)
declare cur cursor for
select a.name,b.name,a.xusertype from syscolumns a,systypes b
where a.xusertype=b.xusertype and a.id=object_id(@tbname) order by b.xusertype
open cur
fetch next from cur into @fld,@ftype,@fid
while @@fetch_status=0
begin
if @ftype in('text','ntext')
exec('update '+@tbname+' set '+@fld+'='''' where '+@fld+' is null')
fetch next from cur into @fld,@ftype,@fid
end
close cur
deallocate cur
if exists(select 1 from sysobjects where xtype='p' and name='update_null')
drop proc update_null
go
/*
功能:处理表test字段中的NULL值,用''替换
作者:WGS
创建时间:2006-06-14
调用事例:
1。对一个表操作:update 'tb1'
2。对整个数据库操作:sp_msforeachtable "update '?'"
*/
create proc update_null(@tbname nvarchar(50))
as
declare @fld varchar(20),@ftype varchar(30),@fid int,@sql varchar(300)
declare cur cursor for
select a.name,b.name,a.xusertype from syscolumns a,systypes b
where a.xusertype=b.xusertype and a.id=object_id(@tbname) order by b.xusertype
open cur
fetch next from cur into @fld,@ftype,@fid
while @@fetch_status=0
begin
if @ftype in('text','ntext')
exec('update '+@tbname+' set '+@fld+'='''' where '+@fld+' is null')
fetch next from cur into @fld,@ftype,@fid
end
close cur
deallocate cur
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'update ['+b.name+'] set ['+a.name+']='''' where ['+a.name+'] is NULL;' from syscolumns a,sysobjects b,systypes c where a.id=b.id and b.xtype='U' and a.xtype=c.xtype and c.name in('text')
exec(@sql)
--try
if exists(select 1 from sysobjects where xtype='p' and name='update_null')
drop proc update_null
go
/*
功能:处理表中"text"和"ntext"类型字段中的NULL值,用''替换
作者:WGS
创建时间:2006-06-14
调用事例:
1。对一个表操作:update_null 'tb1'
2。对整个数据库操作:sp_msforeachtable "update_null '?'"
*/
create proc update_null(@tbname nvarchar(50))
as
declare @fld varchar(20),@ftype varchar(30),@fid int,@sql varchar(300)
declare cur cursor for
select a.name,b.name,a.xusertype from syscolumns a,systypes b
where a.xusertype=b.xusertype and a.id=object_id(@tbname) order by b.xusertype
open cur
fetch next from cur into @fld,@ftype,@fid
while @@fetch_status=0
begin
if @ftype in('text','ntext')
exec('update '+@tbname+' set '+@fld+'='''' where '+@fld+' is null')
fetch next from cur into @fld,@ftype,@fid
end
close cur
deallocate cur