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

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

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

SQL code


ALTER PROCEDURE [dbo].[cs] 
    @id int,@xm varchar(50),@bday DateTime,@tel varchar(50),@qq varchar(50)
AS
BEGIN
    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 null
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

------解决方案--------------------
多次更新也可以的。

------解决方案--------------------

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 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))
AS
BEGIN
    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

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

相关推荐