ALTER PROCEDURE [dbo].[KM008_InsertOrUpdate]
----商谈计划
--商谈计划编码
@BargainSchID bigint,
--保密级别
@SecretLevel char(2),
--相关客户
@ClientID varchar(20),
--相关联系人
@KeyPersonID varchar(20),
--主题
@BargainSubject nvarchar(50),
--类型
@BargainType char(2),
--执行者
@BargainExecutant nvarchar(50),
--优先度
@BargainPriority char(2),
--描述
@BargainDescription nvarchar(500), --9
--开始日期
@BargainFromDate datetime,
--开始时间
@BargainFromTime nvarchar(10),
--结束日期
@BargainEndDate datetime,
--结束时间
@BargainEndTime nvarchar(10),
--执行状态
@ExecuteStat char(2),
--行版本
@RowVer timestamp,
--更新者
@UpdateUserID nvarchar(10),
--主表操作标志
@PriTableMode nvarchar(20), --17
----商谈计划明细
--执行编码
@BargainExeIDArray varchar(8000),
--执行者
@ExecutantNameArray varchar(8000),
--执行日
@ExecuteDateArray varchar(8000),
--执行起止日期
@ExecuteTimeArray varchar(8000),
--执行状况
@ExecuteStatArray varchar(8000),
--操作标志
@ExeTableMode varchar(8000),
--自定义返回值
@OutParam char(1) output,
--返回主键
@ReturnBargainSchID bigint output
AS
declare @E_SUCCESS varchar(1) set @E_SUCCESS='0' --0：正常结束
declare @E_NODATA varchar(1) set @E_NODATA='1' --1：对象数据不存在，检索不到数据
declare @E_ERROR2 varchar(1) set @E_ERROR2='6' --6：主键重复
declare @E_ERROR3 varchar(1) set @E_ERROR3='7' --7：逻辑排他
declare @E_ERROR4 varchar(1) set @E_ERROR4='8' --8：业务排他
declare @E_ERROR5 varchar(1) set @E_ERROR5='9' --9：异常终止
declare @SystemTime datetime --数据库时间
----明细表
declare @Array_BargainExeID bigint
declare @Array_ExecutantName nvarchar(50)
declare @Array_ExecuteDate nvarchar(50)
declare @Array_ExecuteTime nvarchar(50)
declare @Array_ExecuteStat nvarchar(50)
declare @Array_ExeTableMode nvarchar(50)
declare @string7 nvarchar(50)
declare @string8 nvarchar(50)
--存储过程开始
BEGIN try
BEGIN transaction TransUpdateOrInsert
--设置默认返回值
SET @OutParam = @E_SUCCESS
SET @SystemTime=getdate()
--项目详细检索
IF @PriTableMode = 'Update'
BEGIN
UPDATE KM012ConPlan
SET
SecretLevel=@SecretLevel,
ClientID=@ClientID,
KeyPersonID=@KeyPersonID,
BargainSubject=@BargainSubject,
BargainType=@BargainType,
BargainExecutant=@BargainExecutant,
BargainPriority=@BargainPriority,
BargainDescription=@BargainDescription,
BargainFromDate=@BargainFromDate,
BargainFromTime=@BargainFromTime,
BargainEndDate=@BargainEndDate,
BargainEndTime=@BargainEndTime,
ExecuteStat=@ExecuteStat,
UpdateUserID=@UpdateUserID,
UpdatedDate= @SystemTime
WHERE BargainSchID=@BargainSchID And RowVer=@RowVer
if @@Rowcount=0
begin
ROLLBACK Transaction TransUpdateOrInsert
set @OutParam=@E_ERROR3 return
end
END
----商谈计划
IF @PriTableMode = 'Insert'
BEGIN
INSERT Into KM012ConPlan
(
SecretLevel,
ClientID,
KeyPersonID,
BargainSubject,
BargainType,
BargainExecutant,
BargainPriority,
BargainDescription,
BargainFromDate,
BargainFromTime,
BargainEndDate,
BargainEndTime,
ExecuteStat,
DeleteFlg,
CreatedUserID,
CreatedDate,
UpdateUserID,
UpdatedDate
)
VALUES
(
@SecretLevel,
@ClientID,
@KeyPersonID,
@BargainSubject,
@BargainType,
@BargainExecutant,
@BargainPriority,
@BargainDescription,
@BargainFromDate,
@BargainFromTime,
@BargainEndDate,
@BargainEndTime,
@ExecuteStat,
'0',
@UpdateUserID,
@SystemTime,
@UpdateUserID,
@SystemTime
)
if @@Rowcount=0
begin
ROLLBACK Transaction TransUpdateOrInsert
set @OutParam=@E_ERROR3 return
end
else
begin
set @BargainSchID=@@Identity
set @ReturnBargainSchID=@BargainSchID
Print @BargainSchID
end
END
----商谈计划明细
if @ExeTableMode is null or @ExeTableMode= ''
begin
SET @OutParam = @E_SUCCESS
Commit transaction TransUpdateOrInsert
return
end
else
begin
declare curRestHotelDish cursor for
select * from dbo.SplitList('>',@BargainExeIDArray,@ExecutantNameArray,@ExecuteDateArray,@ExecuteTimeArray,@ExecuteStatArray,@ExeTableMode,'','')
--打开游标
OPEN curRestHotelDish
--取出游标数据
FETCH NEXT FROM curRestHotelDish INTO @Array_BargainExeID,@Array_ExecutantName,@Array_ExecuteDate,@Array_ExecuteTime,@Array_ExecuteStat,@Array_ExeTableMode,@string7,@string8
--数据取得失败的场合
IF @@FETCH_STATUS <> 0
BEGIN
SET @OutParam=2
CLOSE curRestHotelDish
DEALLOCATE curRestHotelDish
return
END
--循环取得数据
WHILE @@FETCH_STATUS = 0
BEGIN
if @Array_ExeTableMode='DELETE'
BEGIN
Delete From KM013ConPlanExcD
WHERE KM013ConPlanExcD.BargainExeID=@Array_BargainExeID
--Update KM013ConPlanExcD
--Set
--DeleteFlg='1',
--UpdateUserID=@UpdateUserID,
--UpdatedDate=@SystemTime
--WHERE KM013ConPlanExcD.BargainExeID=@Array_BargainExeID
END
if @Array_ExeTableMode='INSERT'
BEGIN
INSERT KM013ConPlanExcD
(
BargainSchID,
ExecutantName,
ExecuteDate ,
ExecuteTime,
ExecuteStat,
DeleteFlg,
CreatedUserID,
CreatedDate,
UpdateUserID,
UpdatedDate
)
VALUES
(
@BargainSchID,
@Array_ExecutantName,
@Array_ExecuteDate ,
@Array_ExecuteTime,
@Array_ExecuteStat,
'0',
@UpdateUserID,
@SystemTime,
@UpdateUserID,
@SystemTime
)
END
if @Array_ExeTableMode='UPDATE'
BEGIN
Update KM013ConPlanExcD
Set
ExecutantName=@Array_ExecutantName,
ExecuteDate =@Array_ExecuteDate,
ExecuteTime =@Array_ExecuteTime,
ExecuteStat =@Array_ExecuteStat,
UpdateUserID=@UpdateUserID,
UpdatedDate=@SystemTime
Where KM013ConPlanExcD.BargainExeID=@Array_BargainExeID
END
--取数据
FETCH NEXT FROM curRestHotelDish INTO @Array_BargainExeID,@Array_ExecutantName,@Array_ExecuteDate,@Array_ExecuteTime,@Array_ExecuteStat,@Array_ExeTableMode,@string7,@string8
END
END
Commit transaction TransUpdateOrInsert
set @OutParam=@E_SUCCESS
set @ReturnBargainSchID=@BargainSchID
--存储过程结束
END try
--捕获异常
BEGIN catch
Begin
--设置异常
set @OutParam=@E_ERROR5
if cursor_status('local','curRestHotelDish')<> -3
begin
CLOSE curRestHotelDish
DEALLOCATE curRestHotelDish
end
ROLLBACK Transaction TransUpdateOrInsert
End
END catch
发表于 @ 2008年01月17日 17:23:00|评论(loading...)|编辑