存储过程中Update表中记录全为空的字段,该如何解决

存储过程中Update表中记录全为空的字段
这是一个临时表, 我要把全为空的字段显示Error提示, 以下是个例子.
我的问题是: 实际表中有32个字段, 我写了太多的select造成程序调用存储过程没有返回数据, 尽管SET NOCOUNT on
求好心人给个解决办法, 谢谢

SQL code

ALTER procedure [dbo].[TEST]asbegin    declare @t table    (        a varchar(max),        b varchar(max),        c varchar(max)    )    insert into @t values('asd','','')    insert into @t values('','','')    insert into @t values('','','')    select a from @t where a<>''    if @@rowcount = 0 update @t set a = 'Error'    select b from @t where b<>''    if @@rowcount = 0 update @t set b = 'Error'    select c from @t where c<>''    if @@rowcount = 0 update @t set c = 'Error'        SET NOCOUNT on    select * from @tend

SQL code

    declare @str varchar(2000)set @str=''declare @sql varchar(2000)set @sql=''select @str=isnull(@str,',')+'update test set '+name+'='+QUOTENAME('error','''')+' where '+name+' is null'+char(10)from syscolumns where id=object_id('test') exec(@str)--把test换成你的表明即可
[解决办法]
SQL code
ALTER procedure [dbo].[TEST]asbegin    declare @t table    (        a varchar(max),        b varchar(max),        c varchar(max)    )    insert into @t values('asd','','')    insert into @t values('','','')    insert into @t values('','','')    update @t    set a=case when not exists(select 1 from @t where a<>'') then 'Error' else a end,        b=case when not exists(select 1 from @t where b<>'') then 'Error' else b end,        c=case when not exists(select 1 from @t where c<>'') then 'Error' else c end    select * from @tend
[解决办法]
SQL code
USE Testgo--IF object_id('t') IS NOT NULL--    DROP TABLE tCREATE TABLE t(    a varchar(max),    b varchar(max),    c varchar(max),    d varchar(max),    e varchar(max),    f varchar(max),    g varchar(max),    h varchar(max))insert into t values('asd','','','','','','','')insert into t values('','','','','','','','')insert into t values('','','','','','','','')DECLARE @Sql NVARCHAR(MAX)SELECT @Sql=ISNULL(@Sql+NCHAR(13)+NCHAR(10),'')+'if not exists(select 1 from t where '+name+'<>'''') update t set '+name+'=''Error''' FROM sys.syscolumns WHERE id=object_id('t')EXEC (@Sql)SET NOCOUNT onselect * from t