--直接: CREATE PROCEDURE ddf_insert @CodeNum int,@Name varchar(50),@Re varchar(500),@is_del char(1) AS delete from ddf_ply1 where CodeNum=@CodeNum insert into ddf_ply1(CodeNum,[Name],Re)values(@CodeNum,@Name,@Re) GO
--上面没有判断@is_del CREATE PROCEDURE ddf_insert @CodeNum int,@Name varchar(50),@Re varchar(500),@is_del char(1) AS delete from ddf_ply1 where CodeNum=@CodeNum if(@@rowcount=0) insert into ddf_ply1(CodeNum,[Name],Re)values(@CodeNum,@Name,@Re) else if(@is_del=-1) insert into ddf_ply1(CodeNum,[Name],Re)values(@CodeNum,@Name,@Re) GO
--简化下: CREATE PROCEDURE ddf_insert @CodeNum int,@Name varchar(50),@Re varchar(500),@is_del char(1) AS delete from ddf_ply1 where CodeNum=@CodeNum if(@@rowcount=0 || @is_del=-1) insert into ddf_ply1(CodeNum,[Name],Re)values(@CodeNum,@Name,@Re) GO
select @returnNum=count(BH) from ddf_ply1 where CodeNum=@CodeNum 改为 if exist(select count(BH) from ddf_ply1 where CodeNum=@CodeNum )
好像没什么好优化的,就是看着有点别扭……CREATE PROCEDURE ddf_insert @CodeNum int,@Name varchar(50),@Re varchar(500),@is_del char(1) AS if exists (select 1 from ddf_ply1 where CodeNum=@CodeNum) begin if @is_del='1' delete from ddf_ply1 where CodeNum=@CodeNum else update ddf_ply1 set Name=@Name,Re=@Re where CodeNum=@CodeNum end else insert into ddf_ply1(CodeNum,[Name],Re)values(@CodeNum,@Name,@Re) go
--上面没有判断@is_del
CREATE PROCEDURE ddf_insert
@CodeNum int,@Name varchar(50),@Re varchar(500),@is_del char(1)
AS
delete from ddf_ply1 where CodeNum=@CodeNum
if(@@rowcount=0)
insert into ddf_ply1(CodeNum,[Name],Re)values(@CodeNum,@Name,@Re)
else if(@is_del=-1)
insert into ddf_ply1(CodeNum,[Name],Re)values(@CodeNum,@Name,@Re)
GO
--简化下:
CREATE PROCEDURE ddf_insert
@CodeNum int,@Name varchar(50),@Re varchar(500),@is_del char(1)
AS
delete from ddf_ply1 where CodeNum=@CodeNum
if(@@rowcount=0 || @is_del=-1)
insert into ddf_ply1(CodeNum,[Name],Re)values(@CodeNum,@Name,@Re)
GO
if exist(select count(BH) from ddf_ply1 where CodeNum=@CodeNum )
if exists (select 1 from ddf_ply1 where CodeNum=@CodeNum)
begin
if @is_del='1'
delete from ddf_ply1 where CodeNum=@CodeNum
else
update ddf_ply1 set Name=@Name,Re=@Re where CodeNum=@CodeNum
end
else
insert into ddf_ply1(CodeNum,[Name],Re)values(@CodeNum,@Name,@Re)
go