当前位置: 代码迷 >> Sql Server >> 多个字段,不空时不能更新,空时容许更新,有简单的写法吗
  详细解决方案

多个字段,不空时不能更新,空时容许更新,有简单的写法吗

热度:60   发布时间:2016-04-27 11:38:03.0
多个字段,不空时不能更新,空时允许更新,有简单的写法吗?
下面写的是一个存储过程,当字段比较多时,需多次执行更新指令,有没有更简练、效率更高的写法?

大家都来探讨一下,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 [email protected] where [email protected] and xm is null    update table1 set [email protected] where [email protected] and bday is null    update table1 set [email protected] where [email protected] and tel is null    update table1 set [email protected] where [email protected] 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 [email protected]
------解决方案--------------------
多次更新也可以的。
------解决方案--------------------
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 [email protected]


对于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 @[email protected]+case when xm is null then ' [email protected]+''',' end                    +case when bday is null then ' bday='''+convert(varchar(30),@bday,120)+''',' end                    +case when tel is null then ' [email protected]+''',' end                    +case when qq is null then ' [email protected]+''',' end    from table1 where [email protected]        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 [email protected]
  相关解决方案