存储过程中Update表中记录全为空的字段

[jiangseraph]

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

ALTER procedure [dbo].[TEST]
as
begin

	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 @t
end

[jiangseraph]

谢谢楼上各位, 采用了 josy的建议, 没有使用动态SQL

TravyLee 的回复 好象和我要求有出入,我希望的数据如下

a b c
---------------------------
asd error error
error error
error error
总之谢谢大家了, 结帖了!!

[开着拖拉机泡妞]

    
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换成你的表明即可

[百年树人]

ALTER procedure [dbo].[TEST]
as
begin

    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 @t
end

[开着拖拉机泡妞]

---->>TravyLee生成测试数据
create table  test
    (
        a varchar(20),
        b varchar(20),
        c varchar(20)
    )

    insert into test values('asd','','')
    insert into test values('','','')
    insert into test values('','','')
    
declare @str varchar(2000)
set @str=''
declare @sql varchar(2000)
set @sql=''
select @str=isnull(@str,',')+'update test set '+name+'='+QUOTENAME('error','''')+
' where '+name+' = '+''''''+char(10)
from syscolumns where id=object_id('test') 
exec(@str)
--print @str
select * from test

/*
a	b	c
---------------------------
asd	error	error
error	error	error
error	error	error
*/

[我腫了]

USE Test
go


--IF object_id('t') IS NOT NULL
--	DROP TABLE t
CREATE 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 on
select * from t

[我腫了]

USE Test
go


--IF object_id('t') IS NOT NULL
--	DROP TABLE t
CREATE 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+',','')+name+'=case when not exists(select 1 from t where '+name+'<>'''') then ''Error'' else '+name+' end' FROM sys.syscolumns WHERE id=object_id('t')

EXEC ('update t set '+@Sql)

SET NOCOUNT on
select * from t

这都OK