我自己写了一段存储过程，求高手们指点一二
我自己写了一段存储过程，主要是控制并发的，也不知道行不行得通。求高手们指点一二
CREATE PROCEDURE Savejbxx
@save varchar(20), --判断是添加还是修改标志
@cysy varchar(50),
@cydid varchar(50),
@ypid varchar(50),
@cpmc varchar(50),
@ggxh varchar(50),
@sb varchar(50),
@dwlb varchar(50),
@cpdj varchar(50),
@cysl varchar(50),
@cyjs varchar(50),
@cyman varchar(50),
@cyadress varchar(50),
@chuchangid varchar(50),
@cclb varchar(50),
@cydate varchar(50),
@sydate varchar(50),
@ywshi varchar(50),
@ydbgdate varchar(50),
@fyzt varchar(50),
@jyadress varchar(50),
@sjdw varchar(50),
@sjdwadress varchar(50),
@sjdwfr varchar(50),
@tel varchar(50),
@scdw varchar(50),
@scdwadress varchar(50),
@scdwfw varchar(50),
@bzname varchar(50),
@jyxm varchar(50),
@ip varchar(50) --客户端标志
as
set nocount on--设置游标
set transaction isolation level serializable--设置事务级别
set LOCK_TIMEOUT 2000--设定超时时间
begin transaction
select * from jbxx with (HOLDLOCK)--加锁
select * from jybg with (HOLDLOCK)--加锁
if @save='1' --修改记录并删除现有检验项目
begin
update jbxx set cysy=@cysy,ypid=@ypid,cmpc=@cmpc,ggxh=@ggxh,sb=@sb,dwlb=@dwlb,cpdj=@cpdj,cysl=@cysl,cyjs=@cyjs,cyman=@cyman,cyadress=@cyadress,chuchangid=@chuchangid,cclb=@cclb,cydate=@cydate,sydate=@sydate,ywshi=@ywshi,ydbgdate=@ydbgdate,fyzt=@fyzt,jyadress=@jyadress,sjdw=@sjdw,sjdwadress=@sjdwadress,sjdwfr,tel=@tel,scdw=@scdw,scdwadress=@scdwadress,scdwfw=@scdwfw,bzname=@bzname,jyxm=@jyxm where cydid=@cydid
delete from jybg where cydid=@cydid
insert into jybg (cydid,id,jyxm,jldw,bzyq,fangshi,hege,nohege,trueorfalse,bzhao) select @cydid,id,bzxm,dw,bzyq,fangshi,hege,nohege,lk ,bzhao from lsjy where ip=@ip
end
else if @save='2' --修改记录但不删除现有检验项目
begin
update jbxx set cysy=@cysy,ypid=@ypid,cmpc=@cmpc,ggxh=@ggxh,sb=@sb,dwlb=@dwlb,cpdj=@cpdj,cysl=@cysl,cyjs=@cyjs,cyman=@cyman,cyadress=@cyadress,chuchangid=@chuchangid,cclb=@cclb,cydate=@cydate,sydate=@sydate,ywshi=@ywshi,ydbgdate=@ydbgdate,fyzt=@fyzt,jyadress=@jyadress,sjdw=@sjdw,sjdwadress=@sjdwadress,sjdwfr,tel=@tel,scdw=@scdw,scdwadress=@scdwadress,scdwfw=@scdwfw,bzname=@bzname,jyxm=@jyxm where cydid=@cydid
end
else --添加记录
begin
insert into jbxx(cysy,cydid,ypid,cpmc,ggxh,sb,dwlb,cpdj,cysl,cyjs,cyman,cyadress,chuchangid,cclb,cydate,sydate,ywshi,ydbgdate,fyzt,jyadress,sjdw,sjdwadress,sjdwfr,tel,scdw,scdwadress,scdwfw,bzname,jyxm) values (@cysy,@cydid,@ypid,@cpmc,@ggxh,@sb,@dwlb,@cpdj,@cysl,@cyjs,@cyman,@cyadress,@chuchangid,@cclb,@cydate,@sydate,@ywshi,@ydbgdate,@fyzt,@jyadress,@sjdw,@sjdwadress,@sjdwfr,@tel,@scdw,@scdwadress,@scdwfw,@bzname,@jyxm)
insert into jybg (cydid,id,jyxm,jldw,bzyq,fangshi,hege,nohege,trueorfalse,bzhao) select @cydid,id,bzxm,dw,bzyq,fangshi,hege,nohege,lk ,bzhao from lsjy where ip=@ip
end
if @@error<>0
begin
rollback tran
end
else
begin
commit tran
end
set transaction isolation level read committed
set nocount off
return -1
GO
[解决办法]
建议去SQL版。
[解决办法]
没有看出来错误，可以行得通的，楼主还是在SQL里面试一下为易。
