ALTER PROCEDURE dbo.ZM003F012_Oper

 @OperateFlag varchar(50),
 
 @LogisticsID int,
 
 @YearMonth varchar(10),
 
 @LogisticsExpense money,
 
 @MaintenanceExpence money,
 
 @OtherExpense money,
 
 @Note varchar(50), 
 
 @CreatedUserID varchar(20),
 
 @CreatedDate datetime,
 
 @RowVer timestamp,
 
 @OutParam varchar(10) 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 @ContractID varchar(20)
  
--存储过程开始 
Begin  Try       

 set @OutParam=@E_SUCCESS
 
 IF @OperateFlag = 'Insert'
  Begin

      --物业支出
      SELECT * FROM ZM031HouseExpend  WHERE LogisticsID=@LogisticsID  AND  YearMonth=@YearMonth  AND DeleteFlg='0'                                         
   if @@Rowcount<>0
    begin
     set @OutParam=@E_ERROR2 return
    end  
    
   DELETE FROM ZM031HouseExpend
   WHERE (LogisticsID = @LogisticsID) AND (YearMonth = @YearMonth) AND
         (DeleteFlg = '1')  
                                 
   INSERT INTO ZM031HouseExpend
         (LogisticsID, YearMonth, LogisticsExpense, MaintenanceExpence, OtherExpense,
         Note, DeleteFlg, CreatedUserID, CreatedDate, UpdateUserID, UpdatedDate)
   VALUES (@LogisticsID,@YearMonth,@LogisticsExpense,@MaintenanceExpence,@OtherExpense,@Note,
          '0',@CreatedUserID,@CreatedDate,@CreatedUserID,@CreatedDate)
   
   if @@Rowcount=0
    begin
     set @OutParam=@E_ERROR3 return
    end
   else
    begin
     set @OutParam=@E_SUCCESS return
    end
   
  End 
  
 IF @OperateFlag = 'Update'
  Begin  
   
   UPDATE ZM031HouseExpend
   SET LogisticsExpense = @LogisticsExpense,
         MaintenanceExpence = @MaintenanceExpence, OtherExpense = @OtherExpense,
         Note = @Note, UpdateUserID = @CreatedUserID,
         UpdatedDate = @CreatedDate
   WHERE (LogisticsID = @LogisticsID) AND (YearMonth = @YearMonth) AND
         (DeleteFlg = '0') AND (RowVer = @RowVer)
   
   if @@Rowcount=0
    begin
     set @OutParam=@E_ERROR3 return
    end
   else
    begin
     set @OutParam=@E_SUCCESS return
    end
  End
 
 IF @OperateFlag = 'UpdateFirst'
  Begin  
   
   UPDATE ZM031HouseExpend
   SET LogisticsExpense = @LogisticsExpense,
         MaintenanceExpence = @MaintenanceExpence, OtherExpense = @OtherExpense,
         Note = @Note, UpdateUserID = @CreatedUserID,
         UpdatedDate = @CreatedDate
   WHERE (LogisticsID = @LogisticsID) AND (YearMonth = @YearMonth) AND
         (DeleteFlg = '0')
   
   if @@Rowcount=0
    begin
     set @OutParam=@E_ERROR3 return
    end
   else
    begin
     set @OutParam=@E_SUCCESS return
    end
  End
  
 IF @OperateFlag = 'Delete'
  Begin  
   
   UPDATE ZM031HouseExpend
   SET DeleteFlg = '1', UpdateUserID = @CreatedUserID,
         UpdatedDate = @CreatedDate
   WHERE (LogisticsID = @LogisticsID) AND (YearMonth = @YearMonth) AND
         (RowVer = @RowVer)
   
   if @@Rowcount=0
    begin
     set @OutParam=@E_ERROR3 return
    end
   else
    begin
     set @OutParam=@E_SUCCESS return
    end
  End
  
 IF @OperateFlag = 'DeleteFirst'
  Begin  
   
   UPDATE ZM031HouseExpend
   SET DeleteFlg = '1', UpdateUserID = @CreatedUserID,
         UpdatedDate = @CreatedDate
   WHERE (LogisticsID = @LogisticsID) AND (YearMonth = @YearMonth)
   
   if @@Rowcount=0
    begin
     set @OutParam=@E_ERROR3 return
    end
   else
    begin
     set @OutParam=@E_SUCCESS return
    end
  End
  
 IF @OperateFlag = 'DeleteIncome'
  
  Begin  
   
   --设置游标
   Begin
 
    DECLARE ContractID_Cursor CURSOR FOR
 
    SELECT  PactID  FROM  ZM032HouseIncome   WHERE  LogisticsID=@LogisticsID AND DeleteFlg='0'

    OPEN ContractID_Cursor
    FETCH NEXT FROM ContractID_Cursor INTO @ContractID
   
    WHILE @@FETCH_STATUS = 0
  
    BEGIN
 
     UPDATE  ZM021Contract   SET  UseStatus = UseStatus-1 WHERE ContractID  =  @ContractID AND DeleteFlg = '0'
   
     FETCH NEXT FROM ContractID_Cursor INTO @ContractID
    END    
    
    CLOSE ContractID_Cursor
    DEALLOCATE ContractID_Cursor 
   End 
   
   UPDATE ZM032HouseIncome
   SET DeleteFlg = '1'
   WHERE (LogisticsID = @LogisticsID) AND (YearMonth = @YearMonth)
   if @@Rowcount=0
    begin
     set @OutParam=@E_ERROR3 return
    end     
  End
  
--存储过程结束                   
END try

--捕获异常
BEGIN catch
   --设置异常
   set @OutParam =@OutParam
END catch

RETURN

发表于 @ 2008年01月17日　17:14:00 | 评论( loading... ) | 编辑| 举报| 收藏

旧一篇:关于给水晶报表命名的的奇怪问题 | 新一篇:存储过程：关于装配Sql语句的一些操作