用子查询代替游标实现多关联表插入

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

/**************************************************************************
程序名称： [HM].[pBuildingNosCopyBuildingNo]
功能描述： 插入BNMDAT表一条数据
参数说明： 参数名称     数据类型   输入/输出   字段说明
   bnm_id      bigint         编号
   bnm_com_id     bigint         公司编号
   bnm_bdm_id     bigint         楼盘编号
   bnm_no      nvarchar(16)       栋座
   bnm_unit_check    int          单元
   bnm_floor_check    int          楼层
   bnm_house_check    int          房屋
   bnm_new_usr_id    bigint         新建用户编号         
返回值： ret       int
   0=成功;1=已存在
变更历史： 作者  日期  版本  变更说明
   黄杨  2009-02-01 V1.0  创建
   王红亮 2009-04-02 V1.2  加事务，去游标
**************************************************************************/
ALTER PROCEDURE [HM].[pBuildingNosCopyBuildingNo]
(
 @bnm_id      bigint,
 @bnm_com_id     bigint,
 @bnm_bdm_id     bigint,
 @bnm_no      nvarchar(16),
 @bnm_unit_check    int,
 @bnm_floor_check   int,
 @bnm_house_check   int,
 @bnm_new_usr_id    bigint
)
AS
 DECLARE @system_datetime datetime;  --系统时间
 DECLARE @new_bnm_id   bigint;   --新栋座编号
 DECLARE @bum_id    bigint;   --单元编号
 DECLARE @new_bum_id   bigint;   --新单元编号
 DECLARE @bfm_floor1   nvarchar(16); --标号楼层1
 DECLARE @bfm_floor2   nvarchar(16); --标号楼层2
 DECLARE @bhm_low_bfm_id  bigint;   --楼层编号1
 DECLARE @bhm_high_bfm_id bigint;   --楼层编号2
 DECLARE @bhm_id    bigint;   --房屋编号 
BEGIN
 SET @system_datetime = GETDATE();  --获取系统时间
 SET @new_bnm_id   = 0;    --初始化新栋座编号
 SET @bum_id    = 0;    --初始化单元编号
 SET @new_bum_id   = 0;    --初始化新单元编号
 SET @bfm_floor1   = '';    --初始化标号楼层1
 SET @bfm_floor2   = '';    --初始化标号楼层2
 SET @bhm_low_bfm_id  = 0;    --初始化楼层编号1
 SET @bhm_high_bfm_id = 0;    --初始化楼层编号2
 SET @bhm_id    = 0;    --初始化房屋编号
--插入栋座
 IF EXISTS ( SELECT 1
    FROM HM.BNMDAT
    WHERE bnm_bdm_id  = @bnm_bdm_id
    AND  bnm_com_id  = @bnm_com_id
    AND  bnm_no   = @bnm_no
    )
  RETURN 1 --Key Duplicate

 IF NOT EXISTS ( SELECT 1
      FROM HM.BDMMAS
      WHERE bdm_id = @bnm_bdm_id
     )
  RETURN 9 --楼盘编号不存在
  
 BEGIN TRAN  --事务开始

  INSERT
  INTO HM.BNMDAT 
    (
     bnm_com_id,
     bnm_bdm_id,
     bnm_bdm_name,
     bnm_no,
     bnm_build_year,
     bnm_build_area,
     bnm_towards,
     bnm_up_floor_cnt,
     bnm_down_floor_cnt,
     bnm_pro_sol_id,
     bnm_pro_sol_name,
     bnm_unit_define_type,
     bnm_fact_unit_cnt,
     bnm_memo,
     bnm_status,
     bnm_new_usr_id,
     bnm_unit_cnt,
     bnm_new_time
    )
  SELECT  A.bnm_com_id,
     A.bnm_bdm_id,
     A.bnm_bdm_name,
     @bnm_no,
     A.bnm_build_year,
     A.bnm_build_area,
     A.bnm_towards,
     A.bnm_up_floor_cnt,
     A.bnm_down_floor_cnt,
     A.bnm_pro_sol_id,
     A.bnm_pro_sol_name,
     A.bnm_unit_define_type,
     A.bnm_fact_unit_cnt,
     A.bnm_memo,
     A.bnm_status,
     @bnm_new_usr_id,
     A.bnm_unit_cnt,
     @system_datetime
  FROM  HM.BNMDAT A   
  WHERE  A.bnm_id = @bnm_id

  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRAN --事务回滚
   COMMIT           --事务结束
   RETURN -11 --返回错误
  END

  SET @new_bnm_id = @@IDENTITY;  --获取新栋座编号
  --新增成功后修改楼盘字典表中的总栋数
  UPDATE HM.BDMMAS
  SET  bdm_no_cnt = bdm_no_cnt +1
  WHERE bdm_id = @bnm_bdm_id;

  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRAN --事务回滚
   COMMIT           --事务结束
   RETURN -10 --返回错误
  END
  
  --插入单元
  IF @bnm_unit_check = 1
  BEGIN

    INSERT 
    INTO HM.BUMDAT
      (
       bum_com_id,
       bum_bdm_id,
       bum_bnm_id,
       bum_unit,
       bum_up_floor_cnt,
       bum_down_floor_cnt,
       bum_floor_define_type,
       bum_fact_floor_cnt,
       bum_house_cnt,
       bum_memo,
       bum_status,
       bum_new_usr_id,
       bum_floor_cnt,
       bum_new_time   
      )
    SELECT  B.bum_com_id,
       B.bum_bdm_id,
       @new_bnm_id,
       B.bum_unit,
       B.bum_up_floor_cnt,
       B.bum_down_floor_cnt,
       B.bum_floor_define_type,
       B.bum_fact_floor_cnt,
       B.bum_house_cnt,
       B.bum_memo,
       B.bum_status,
       @bnm_new_usr_id,
       B.bum_floor_cnt,
       @system_datetime 
    FROM  HM.BUMDAT B
    WHERE  B.bum_bnm_id  = @bnm_id

    IF @@ERROR <> 0
    BEGIN
     ROLLBACK TRAN --事务回滚
     COMMIT           --事务结束
     RETURN -12 --返回错误
    END

    --插入楼层
    IF @bnm_floor_check = 1
    BEGIN
     INSERT 
     INTO HM.BFMDAT
       (
        bfm_com_id,
        bfm_bdm_id,
        bfm_bnm_id,
        bfm_bum_id,
        bfm_floor,
        bfm_physics_floor,
        bfm_memo,
        bfm_exist,
        bfm_house_cnt,
        bfm_height,
        bfm_status,
        bfm_new_usr_id,
        bfm_new_time   
       )
     SELECT  C.bfm_com_id,
        C.bfm_bdm_id,
        @new_bnm_id,
        (SELECT bum_id FROM HM.BUMDAT WHERE bum_unit=(SELECT bum_unit FROM HM.BUMDAT WHERE bum_id=C.bfm_bum_id) and bum_bnm_id=@new_bnm_id) AS bfm_bum_id,       
        C.bfm_floor,
        C.bfm_physics_floor,
        C.bfm_memo,
        C.bfm_exist,
        C.bfm_house_cnt,
        C.bfm_height,
        C.bfm_status,
        @bnm_new_usr_id,
        @system_datetime
     FROM  HM.BFMDAT C
     WHERE  C.bfm_bnm_id = @bnm_id

     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN --事务回滚
      COMMIT           --事务结束
      RETURN -13 --返回错误
     END

     --插入房屋
     IF @bnm_house_check = 1
     BEGIN
       INSERT 
       INTO HM.BHMDAT 
         (
          bhm_bum_id,
          bhm_bdm_id,
          bhm_bnm_id,
          bhm_com_id,
          bhm_low_bfm_id,
          bhm_high_bfm_id,
          bhm_layer,
          bhm_door_no,
          bhm_rooms,
          bhm_halls,
          bhm_toilets,
          bhm_kitchens,
          bhm_fitment_sol_id,
          bhm_build_area,
          bhm_use_area,
          bhm_towards,
          bhm_pro_sol_id,
          bhm_usage,
          bhm_structure_sol_id,
          bhm_memo,
          bhm_status,
          bhm_new_usr_id,
          bhm_new_time   
         )
       SELECT  
          (SELECT bum_id FROM HM.BUMDAT WHERE bum_unit=(SELECT bum_unit FROM HM.BUMDAT WHERE bum_id=F.bhm_bum_id) and bum_bnm_id=@new_bnm_id) AS bhm_bum_id, 
          F.bhm_bdm_id,
          @new_bnm_id,
          F.bhm_com_id,
          (SELECT bfm_id FROM HM.BFMDAT WHERE bfm_floor=(SELECT bfm_floor FROM HM.BFMDAT WHERE bfm_id=F.bhm_low_bfm_id ) and bfm_bnm_id=@new_bnm_id and bfm_bum_id=(SELECT bum_id FROM HM.BUMDAT WHERE bum_unit=(SELECT bum_unit FROM HM.BUMDAT WHERE bum_id=F.bhm_bum_id) and bum_bnm_id=@new_bnm_id)) AS bhm_low_bfm_id,
          (SELECT bfm_id FROM HM.BFMDAT WHERE bfm_floor=(SELECT bfm_floor FROM HM.BFMDAT WHERE bfm_id=F.bhm_high_bfm_id ) and bfm_bnm_id=@new_bnm_id and bfm_bum_id=(SELECT bum_id FROM HM.BUMDAT WHERE bum_unit=(SELECT bum_unit FROM HM.BUMDAT WHERE bum_id=F.bhm_bum_id) and bum_bnm_id=@new_bnm_id)) AS bhm_high_bfm_id,         
          F.bhm_layer,
          F.bhm_door_no,
          F.bhm_rooms,
          F.bhm_halls,
          F.bhm_toilets,
          F.bhm_kitchens,
          F.bhm_fitment_sol_id,
          F.bhm_build_area,
          F.bhm_use_area,
          F.bhm_towards,
          F.bhm_pro_sol_id,
          F.bhm_usage,
          F.bhm_structure_sol_id,
          F.bhm_memo,
          F.bhm_status,
          @bnm_new_usr_id,
          @system_datetime
       FROM  HM.BHMDAT F
       WHERE  F.bhm_bnm_id = @bnm_id

       IF @@ERROR <> 0
       BEGIN
        ROLLBACK TRAN --事务回滚
        COMMIT           --事务结束
        RETURN -14 --返回错误
       END
     END
    END
    ELSE
    BEGIN
     --如果不复制楼层，则采集楼层数置0
     UPDATE HM.BUMDAT
     SET  bum_floor_cnt = 0
     WHERE bum_id   = @new_bum_id

     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN --事务回滚
      COMMIT           --事务结束
      RETURN -12 --返回错误
     END
    END    
  END
  ELSE
  BEGIN
   --如果不复制单元，则采集单元数置0
   UPDATE HM.BNMDAT
   SET  bnm_unit_cnt = 0
   WHERE bnm_id   = @new_bnm_id

   IF @@ERROR <> 0
   BEGIN
    ROLLBACK TRAN --事务回滚
    COMMIT           --事务结束
    RETURN -11 --返回错误
   END
  END
  
 COMMIT  --事务结束 

 RETURN 0
END