SQLSERVER存储过程查找数据表中某列存在空值

ALTER PROCEDURE [MDM].[PRO_CHK_COLUMNS_VALUES]

@TableName nvarchar(50)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @list_column_name nvarchar(150)

DECLARE @sql nvarchar(1000)

DECLARE @StrSQL VARCHAR(1000)

　DECLARE @count int

　　declare cur cursor for --定义游标

    select name from sys.columns  where object_id=OBJECT_ID(''+@TableName+'')

    open cur--打开游标  

fetch next from cur into @list_column_name --把提取操作的列数据放到局部变量中  

while(@@fetch_status=0) --游标读取下一条数据是否成功  

begin

   SET @sql='select @count = COUNT(1) from '+@TableName +' where '+@list_column_name +' like ''% %'''

   exec sp_executesql @sql,N'@count nvarchar(50) out ',@count out 

if @count > 0

begin

--select ltrim(rtrim(COMPANY_CODE)) from MDM.TB_BASE_ORDER_COMB

SET @StrSQL='UPDATE ' +@TableName +' SET ' + @list_column_name + ' = '+ 'ltrim(rtrim(' + @list_column_name + '))' 

PRINT @StrSQL

EXEC(@StrSQL)

end

--读取下一条数据记录放到局部变量中，变量的数目必须与游标选择列表中的列的数目一致

  fetch next from cur into  @list_column_name   

end    

close cur--关闭游标  

deallocate cur--删除游标 

END

转载于:https://www.cnblogs.com/liuzhuqing/p/7480132.html