存储过程 判断参数是否为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


[解决办法]

SQL code

CREATE PROCEDURE cuijiantongzhidan_add    @sybm nvarchar (255),    @sydd nvarchar (255),    @syr nvarchar (255),    @jdyf nvarchar (255) ,    @cjdrq datetimeAS    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)    endGO