存储过程 判断参数是否为null,该如何解决
日期：2014-05-18 浏览次数：20553 次
存储过程 判断参数是否为null
存储过程 判断参数是否为null，根据判断结果，执行不同的数据库操作，未按照设计执行，原因在哪里
CREATE PROCEDURE cuijiantongzhidan_add
@sybm nvarchar (255),
@sydd nvarchar (255),
@syr nvarchar (255),
@jdyf nvarchar (255) ,
@cjdrq datetime
AS
if (@sybm is null and @sydd is null and @syr is null)
begin
Update jdjzjh set cjtzd="1", cjdrq=@cjdrq where (sybm is null) and (sydd is null) and (syr is null) and (jdyf=@jdyf)
End
if (@sybm is not null and @sydd is null and @syr is null)
begin
Update jdjzjh set cjtzd="1", cjdrq=@cjdrq where (sybm=@sybm) and (sydd is null) and (syr is null) and (jdyf=@jdyf)
end
if (@sybm is not null and @sydd is not null and @syr is null)
begin
Update jdjzjh set cjtzd="1", cjdrq=@cjdrq where (sybm=@sybm) and (sydd=@sydd) and (syr is null) and (jdyf=@jdyf)
end
if (@sybm is not null and @sydd is not null and @syr is not null)
begin
Update jdjzjh set cjtzd="1", cjdrq=@cjdrq where (sybm=@sybm) and (sydd=@sydd) and (syr=@syr) and (jdyf=@jdyf)
end
GO
------解决方案--------------------
存储过程 判断参数是否为null，根据判断结果，执行不同的数据库操作，未按照设计执行，原因在哪里
CREATE PROCEDURE cuijiantongzhidan_add
@sybm nvarchar (255),
@sydd nvarchar (255),
@syr nvarchar (255),
@jdyf nvarchar (255) ,
@cjdrq datetime
AS
if (@sybm is null and @sydd is null and @syr is null)
begin
Update jdjzjh set cjtzd="1", cjdrq=@cjdrq where (sybm is null) and (sydd is null) and (syr is null) and (jdyf=@jdyf)
End
if (@sybm is not null and @sydd is null and @syr is null)
begin
Update jdjzjh set cjtzd="1", cjdrq=@cjdrq where (sybm=@sybm) and (sydd is null) and (syr is null) and (jdyf=@jdyf)
end
if (@sybm is not null and @sydd is not null and @syr is null)
begin
Update jdjzjh set cjtzd="1", cjdrq=@cjdrq where (sybm=@sybm) and (sydd=@sydd) and (syr is null) and (jdyf=@jdyf)
end
if (@sybm is not null and @sydd is not null and @syr is not null)
begin
Update jdjzjh set cjtzd="1", cjdrq=@cjdrq where (sybm=@sybm) and (sydd=@sydd) and (syr=@syr) and (jdyf=@jdyf)
end
GO
------解决方案--------------------
- SQL code
CREATE PROCEDURE cuijiantongzhidan_add
@sybm nvarchar (255),
@sydd nvarchar (255),
@syr nvarchar (255),
@jdyf nvarchar (255) ,
@cjdrq datetime
AS
if (IsNull(@sybm,'') ='' and IsNull(@sydd,'') ='' and IsNull(@syr,'') ='')
begin
Update jdjzjh set cjtzd='1', cjdrq=@cjdrq where (IsNull(sybm,'')='') and (isNull(sydd,'')='') and (isNull(syr,'')='') and (jdyf=@jdyf)
End
if (IsNull(@sybm,'') <>'' and IsNull(@sydd,'') ='' and IsNull(@syr,'') ='')
begin
Update jdjzjh set cjtzd='1', cjdrq=@cjdrq where (sybm=@sybm) and (isNull(sydd,'')='') and (isNull(syr,'')='') and (jdyf=@jdyf)
end
if (IsNull(@sybm,'') <>'' and IsNull(@sydd,'') <>'' and IsNull(@syr,'') ='')
begin
Update jdjzjh set cjtzd='1', cjdrq=@cjdrq where (sybm=@sybm) and (sydd=@sydd) and (isNull(syr,'')='') and (jdyf=@jdyf)
end
if (IsNull(@sybm,'') <>'' and IsNull(@sydd,'') <>'' and IsNull(@syr,'') <>'')
begin
Update jdjzjh set cjtzd='1', cjdrq=@cjdrq where (sybm=@sybm) and (sydd=@sydd) and (syr=@syr) and (jdyf=@jdyf)
end
GO
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
