下面写的是一个存储过程,当字段比较多时,需多次执行更新指令,有没有更简练、效率更高的写法?
大家都来探讨一下,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
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
------解决方案--------------------