已测试通过:declare @i int select @i=count(*) from syscolumns where id=object_id(N'表名')declare @name varchar(30) declare @sql varchar(8000) while @i>0 begin select @name=name from syscolumns where id=object_id(N'表名') and colid=@i -- select @name set @sql=' update 表名 set '+@name+'=isnull(' + @name + ','''')'
exec (@sql) set @sql='' set @i=@i-1 end
不管有多少字段都可以,如果写成存储过程,以表名(varchar)为参数,则可重用。
Create proc sp_ConvertNullToSpace @TableName varchar(30) as set nocount on-- declare @TableName varchar(30) -- set @TableName='MstUser'declare @i int select @i=count(*) from syscolumns where id=object_id(@TableName) declare @name varchar(30) declare @sql varchar(8000) while @i>0 begin select @name=name from syscolumns where id=object_id(@TableName) and colid=@i -- select @name set @sql=' update '+@TableName+' set '+@name+'=isnull(' + @name + ','''')'
UPDATE Table1 SET 字段2='' WHERE ISNULL(字段2,'')=''
UPDATE Table1 SET 字段3='' WHERE ISNULL(字段3,'')=''
...
用游标加循环,还有事务锁。
麻烦举个例子好不??小弟先谢谢了...
select @i=count(*) from syscolumns where id=object_id(N'表名')declare @name varchar(30)
declare @sql varchar(8000)
while @i>0
begin
select @name=name from syscolumns where id=object_id(N'表名') and colid=@i
-- select @name set @sql='
update 表名
set '+@name+'=isnull(' + @name + ','''')'
exec (@sql)
set @sql=''
set @i=@i-1
end
@TableName varchar(30)
as set nocount on-- declare @TableName varchar(30)
-- set @TableName='MstUser'declare @i int
select @i=count(*) from syscolumns where id=object_id(@TableName)
declare @name varchar(30)
declare @sql varchar(8000)
while @i>0
begin
select @name=name from syscolumns where id=object_id(@TableName) and colid=@i
-- select @name set @sql='
update '+@TableName+'
set '+@name+'=isnull(' + @name + ','''')'
exec (@sql)
set @sql=''
set @i=@i-1
end
使用你的存储过程怎么在加载时出错了,,-- 将set @TableName='MstUser'改成自己的表名了,其他还需要修改吗???
-- set @TableName='MstUser'是我调试时注释掉的两行,可以不要的。这个存储过程直接用就可以了。exec sp_ConvertNullToSpace 你的表名你试试 :)