多个字段，不空时不能更新，空时允许更新，有简单的写法吗？
下面写的是一个存储过程，当字段比较多时，需多次执行更新指令，有没有更简练、效率更高的写法？

大家都来探讨一下，O(∩_∩)O谢谢~~~

SQL code

ALTER PROCEDURE [dbo].[cs]     @id int,@xm varchar(50),@bday DateTime,@tel varchar(50),@qq varchar(50)ASBEGIN    SET NOCOUNT ON;    update table1 set xm=@xm where id=@id and xm is null    update table1 set bday=@bday where id=@id and bday is null    update table1 set tel=@tel where id=@id and tel is null    update table1 set qq=@qq where id=@id and qq is nullEND

SQL code

update table1 set     xm=isnull(xm,@xm),    bday=isnull(bday,@bday),    tel=isnull(tel,@tel),    qq=isnull(qq,@qq),where id=@id
[解决办法]
多次更新也可以的。
[解决办法]
SQL code
UPDATE  table1SET     xm = ISNULL(xm, @xm), bday = ISNULL(bday, @bday), tel = ISNULL(tel, @tel), qq = ISNULL(qq, @qq)WHERE   id = @id
[解决办法]
探讨


引用:

SQL code

update table1 set
xm=isnull(xm,@xm),
bday=isnull(bday,@bday),
tel=isnull(tel,@tel),
qq=isnull(qq,@qq),
where id=@id


对于4个字段来说，效率和顶楼的是否会相差几乎4倍呢？

[解决办法]
SQL code
ALTER PROCEDURE [dbo].[cs] (@id int, @xm varchar(50), @bday DateTime, @tel varchar(50), @qq varchar(50))ASBEGIN    SET NOCOUNT ON;    declare @sql varchar(6000)        select @sql='update table1 set '        select @sql=@sql+case when xm is null then ' xm='''+@xm+''',' end                    +case when bday is null then ' bday='''+convert(varchar(30),@bday,120)+''',' end                    +case when tel is null then ' tel='''+@tel+''',' end                    +case when qq is null then ' xm='''+@qq+''',' end    from table1 where id=@id        select @sql=left(@sql,len(@sql)-1)+' where id='+rtrim(@id)        exec(@sql)END
[解决办法]
探讨


SQL code

update table1 set 
    xm=isnull(xm,@xm),
    bday=isnull(bday,@bday),
    tel=isnull(tel,@tel),
    qq=isnull(qq,@qq),
where id=@id