日期:2014-05-18  浏览次数:20516 次

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

SQL code

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



------解决方案--------------------
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]
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

------解决方案--------------------
SQL code

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