34,594
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE [dbo].[pos_Qianshou]
(
@orderID varchar(32),
@Signee varchar(10),--签收人
@delivetyName varchar(10),--操作员名称
@deliveryDep varchar(20)--站点
)
AS
begin
--定义变量------------------
declare @tnid int,@count int,@orderMoney money,@Sign varchar(10),@Saccount money
,@oldPoster varchar(20),@mstate int
-------------------------------------------------查询订单信息----------------------
select @tnid=ord.tnid,@Saccount=ord.Yaccount,@oldPoster=Poster,@mstate=mstate from orderinfo ord
where ord.orderid=@orderID
----------------------------------------------获取报单表数据----------------
select @count=count(tnid) from baodan where tnid=@tnid
if(@mstate>=2 and @mstate<=5)
begin
if(@oldPoster!=@delivetyName)--不是同一个投递员
begin
--修改订单表投递员
update orderinfo set poster=@delivetyName,Station=@deliveryDep,Station_HQ=@deliveryDep where tnid=@tnid
-------
update arrive_sp set station=@deliveryDep,station_hq=@deliveryDep,poster=@delivetyName where tnid=@tnid
end
------------------------------------------------------------------------------------------
if(@count>0)--如果此单在报单结算表中存在,则修改实收金额
begin
if(@oldPoster!=@delivetyName)--不是同一个投递员
begin
update baodan set [Sign]=@Signee,Soperater=@delivetyName,BMDATE=getdate(),oldPoster=@oldPoster
where tnid=@tnid
end
else begin
--如果此单在报单结算表中存在,则修改实收金额
update baodan set [Sign]=@Signee,Soperater=@delivetyName,BMDATE=getdate()
where tnid=@tnid
end
end
else begin
--在报单结算表中添加一条
declare @ArriveTime datetime,@LeadTime datetime,@Yaccount money
select @ArriveTime=ar.ArriveTime,@LeadTime=ar.LeadTime,@Yaccount=ord.Yaccount
from orderinfo ord
left join arrive_sp ar on ar.tnid=ord.tnid
where ord.orderid=@orderid
-------------------------添加----------------------------
Insert into baodan(tnid,ArriveTime,LeadTime,BackTIME,Yaccount,Saccount,Sign_status
,[Sign],Soperater,BMDATE,oldPoster)
Values(@tnid,@ArriveTime,@LeadTime,getdate(),@Yaccount,@orderMoney,0,
@Signee,@delivetyName,getdate(),@delivetyName)
end
--最后修改orderinfo表中Status,Mstate
update orderinfo set Status='T',Mstate=6,tmemo='pos'+tmemo where tnid=@tnid
end
end
GO
begin tran
...
commit tran
if @@error>0
rollback
CREATE PROCEDURE [dbo].[pos_Qianshou]
(
@orderID varchar(32),
@Signee varchar(10),--签收人
@delivetyName varchar(10),--操作员名称
@deliveryDep varchar(20)--站点
)
AS
begin
begin tran --开启事务
begin try
......
commit tran -- 提交事务
end try
begin catch -- 捕获异常
......
rollback tran -- 回滚事务
end catch
end
CREATE PROCEDURE [dbo].[pos_Qianshou]
(
@orderID varchar(32),
@Signee varchar(10),--签收人
@delivetyName varchar(10),--操作员名称
@deliveryDep varchar(20)--站点
)
AS
begin
begin tran
begin try
......
commit tran
end try
begin catch
......
rollback tran
end catch
end
CREATE PROCEDURE [dbo].[pos_Qianshou]
(
@orderID varchar(32),
@Signee varchar(10),--签收人
@delivetyName varchar(10),--操作员名称
@deliveryDep varchar(20)--站点
)
AS
set xact_abort on
begin TRAN
--定义变量------------------
declare @tnid int,@count int,@orderMoney money,@Sign varchar(10),@Saccount money
,@oldPoster varchar(20),@mstate int,@numError int
-------------------------------------------------查询订单信息----------------------
select @tnid=ord.tnid,@Saccount=ord.Yaccount,@oldPoster=Poster,@mstate=mstate from orderinfo ord
where ord.orderid=@orderID
----------------------------------------------获取报单表数据----------------
select @count=count(tnid) from baodan where tnid=@tnid
if(@mstate>=2 and @mstate<=5)
begin--开始
if(@oldPoster!=@delivetyName)--不是同一个投递员
begin
--修改订单表投递员
update orderinfo set poster=@delivetyName,Station=@deliveryDep,Station_HQ=@deliveryDep where tnid=@tnid
-------
update arrive_sp set station=@deliveryDep,station_hq=@deliveryDep,poster=@delivetyName where tnid=@tnid
end
------------------------------------------------------------------------------------------
if(@count>0)--如果此单在报单结算表中存在,则修改实收金额
begin
if(@oldPoster!=@delivetyName)--不是同一个投递员
begin
update baodan set [Sign]=@Signee,Soperater=@delivetyName,BMDATE=getdate(),oldPoster=@oldPoster
where tnid=@tnid
end
else begin
--如果此单在报单结算表中存在,则修改实收金额
update baodan set [Sign]=@Signee,Soperater=@delivetyName,BMDATE=getdate()
where tnid=@tnid
end
end
else begin
--在报单结算表中添加一条
declare @ArriveTime datetime,@LeadTime datetime,@Yaccount money
select @ArriveTime=ar.ArriveTime,@LeadTime=ar.LeadTime,@Yaccount=ord.Yaccount
from orderinfo ord
left join arrive_sp ar on ar.tnid=ord.tnid
where ord.orderid=@orderid
-------------------------添加----------------------------
Insert into baodan(tnid,ArriveTime,LeadTime,BackTIME,Yaccount,Saccount,Sign_status
,[Sign],Soperater,BMDATE,oldPoster)
Values(@tnid,@ArriveTime,@LeadTime,getdate(),@Yaccount,@orderMoney,0,
@Signee,@delivetyName,getdate(),@delivetyName)
end
--最后修改orderinfo表中Status,Mstate
update orderinfo set Status='T',Mstate=6,tmemo='pos'+tmemo where tnid=@tnid
end--结束
COMMIT TRAN
GO