表数据null值清除

最新推荐文章于 2021-12-18 19:52:41 发布

最新推荐文章于 2021-12-18 19:52:41 发布 · 355 阅读

本文介绍了一种清除表中null值的方法，通过SQL查询和更新语句实现数据完整性。

-- ============================================= -- Author: <Michael Wang> -- Create date: <2010-07-28> -- Description: <表数据null值清除> -- ============================================= ALTER procedure [dbo].[ClearNull] @tablename varchar(20) as begin BEGIN TRANSACTION declare @str1 varchar(5000) declare @str2 varchar(5000) declare @p1 varchar(20) declare @p2 varchar(20) set @str1='select a.name as name,b.name as descr into cmsmq_temp from syscolumns a LEFT JOIN systypes b ON a.xtype=b.xtype' set @str1=@str1+' LEFT JOIN sysobjects c ON a.id=c.id where c.name='+''''+@tablename+'''' exec(@str1) declare cmssor CURSOR for select * from cmsmq_temp open cmssor fetch next from cmssor into @p1,@p2 WHILE @@FETCH_STATUS = 0 begin if @p2='char' or @p2='varchar' or @p2='nchar' set @str2='update '+@tablename+' set '+@p1+'='+''''+''''+' where '+@p1+' is null' else set @str2='update '+@tablename+' set '+@p1+'='+''''+''''+' where '+@p1+' is null' print @str2 exec(@str2) fetch next from cmssor into @p1,@p2 end close cmssor deallocate cmssor end drop table cmsmq_temp COMMIT TRANSACTION