存储过程 判断参数是否为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", [email protected] where (sybm is null) and (sydd is null) and (syr is null) and ([email protected])
End
if (@sybm is not null and @sydd is null and @syr is null)
begin
Update jdjzjh set cjtzd="1", [email protected] where ([email protected]) and (sydd is null) and (syr is null) and ([email protected])
end
if (@sybm is not null and @sydd is not null and @syr is null)
begin
Update jdjzjh set cjtzd="1", [email protected] where ([email protected]) and ([email protected]) and (syr is null) and ([email protected])
end
if (@sybm is not null and @sydd is not null and @syr is not null)
begin
Update jdjzjh set cjtzd="1", [email protected] where ([email protected]) and ([email protected]) and ([email protected]) and ([email protected])
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', [email protected] where (IsNull(sybm,'')='') and (isNull(sydd,'')='') and (isNull(syr,'')='') and ([email protected]) End if (IsNull(@sybm,'') <>'' and IsNull(@sydd,'') ='' and IsNull(@syr,'') ='') begin Update jdjzjh set cjtzd='1', [email protected] where ([email protected]) and (isNull(sydd,'')='') and (isNull(syr,'')='') and ([email protected]) end if (IsNull(@sybm,'') <>'' and IsNull(@sydd,'') <>'' and IsNull(@syr,'') ='') begin Update jdjzjh set cjtzd='1', [email protected] where ([email protected]) and ([email protected]) and (isNull(syr,'')='') and ([email protected]) end if (IsNull(@sybm,'') <>'' and IsNull(@sydd,'') <>'' and IsNull(@syr,'') <>'') begin Update jdjzjh set cjtzd='1', [email protected] where ([email protected]) and ([email protected]) and ([email protected]) and ([email protected]) endGO