cretrg.txt

北京铁路局住房分配系统,数据库为MSSql2000,依次执行crebas4.sql

/*==============================================================*/
/* Database name:  PhysicalDataModel_1                          */
/* DBMS name:      Microsoft SQL Server 2000                    */
/* Created on:     2002-8-2 11:29:06                            */
/*==============================================================*/


if exists (select 1
          from sysobjects
          where name = 'P_ZGFPB_Save'
          and type = 'P')
   drop procedure P_ZGFPB_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_ZGFPB_Del'
          and type = 'P')
   drop procedure P_ZGFPB_Del
go


if exists (select 1
          from sysobjects
          where name = 'P_ZFXX_VIEW'
          and type = 'P')
   drop procedure P_ZFXX_VIEW
go


if exists (select 1
          from sysobjects
          where name = 'P_ZFXX_Save'
          and type = 'P')
   drop procedure P_ZFXX_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_ZFXX_Del'
          and type = 'P')
   drop procedure P_ZFXX_Del
go


if exists (select 1
          from sysobjects
          where name = 'P_YTFPB_Save'
          and type = 'P')
   drop procedure P_YTFPB_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_YTFPB_Del'
          and type = 'P')
   drop procedure P_YTFPB_Del
go


if exists (select 1
          from sysobjects
          where name = 'P_Xqxx_Save'
          and type = 'P')
   drop procedure P_Xqxx_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_XQXX_Del'
          and type = 'P')
   drop procedure P_XQXX_Del
go


if exists (select 1
          from sysobjects
          where name = 'P_UserSave'
          and type = 'P')
   drop procedure P_UserSave
go


if exists (select 1
          from sysobjects
          where name = 'P_UserLogin'
          and type = 'P')
   drop procedure P_UserLogin
go


if exists (select 1
          from sysobjects
          where name = 'P_UserDel'
          and type = 'P')
   drop procedure P_UserDel
go


if exists (select 1
          from sysobjects
          where name = 'P_TestZG1'
          and type = 'P')
   drop procedure P_TestZG1
go


if exists (select 1
          from sysobjects
          where name = 'P_TestZG0'
          and type = 'P')
   drop procedure P_TestZG0
go


if exists (select 1
          from sysobjects
          where name = 'P_TestZFXXB'
          and type = 'P')
   drop procedure P_TestZFXXB
go


if exists (select 1
          from sysobjects
          where name = 'P_TestYTB'
          and type = 'P')
   drop procedure P_TestYTB
go


if exists (select 1
          from sysobjects
          where name = 'P_TestHX'
          and type = 'P')
   drop procedure P_TestHX
go


if exists (select 1
          from sysobjects
          where name = 'P_TestFJ'
          and type = 'P')
   drop procedure P_TestFJ
go


if exists (select 1
          from sysobjects
          where name = 'P_TestCX'
          and type = 'P')
   drop procedure P_TestCX
go


if exists (select 1
          from sysobjects
          where name = 'P_Test'
          and type = 'P')
   drop procedure P_Test
go


if exists (select 1
          from sysobjects
          where name = 'P_TXFP_Save'
          and type = 'P')
   drop procedure P_TXFP_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_SYS4_Save'
          and type = 'P')
   drop procedure P_SYS4_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_SYS4_Del'
          and type = 'P')
   drop procedure P_SYS4_Del
go


if exists (select 1
          from sysobjects
          where name = 'P_SYS3_Save'
          and type = 'P')
   drop procedure P_SYS3_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_SYS3_Del'
          and type = 'P')
   drop procedure P_SYS3_Del
go


if exists (select 1
          from sysobjects
          where name = 'P_SYS2_Save'
          and type = 'P')
   drop procedure P_SYS2_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_SYS2_Del'
          and type = 'P')
   drop procedure P_SYS2_Del
go


if exists (select 1
          from sysobjects
          where name = 'P_SYS1_Save'
          and type = 'P')
   drop procedure P_SYS1_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_SYS1_Del'
          and type = 'P')
   drop procedure P_SYS1_Del
go


if exists (select 1
          from sysobjects
          where name = 'P_SYS0_Save'
          and type = 'P')
   drop procedure P_SYS0_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_SYS0_Del'
          and type = 'P')
   drop procedure P_SYS0_Del
go


if exists (select 1
          from sysobjects
          where name = 'P_Lyxx_Save'
          and type = 'P')
   drop procedure P_Lyxx_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_LYXX_Del'
          and type = 'P')
   drop procedure P_LYXX_Del
go


if exists (select 1
          from sysobjects
          where name = 'P_GRXX_Save'
          and type = 'P')
   drop procedure P_GRXX_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_GRXX_Del'
          and type = 'P')
   drop procedure P_GRXX_Del
go


if exists (select 1
          from sysobjects
          where name = 'P_FPZB_Save'
          and type = 'P')
   drop procedure P_FPZB_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_FJFPB_Save'
          and type = 'P')
   drop procedure P_FJFPB_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_DWXX_Save'
          and type = 'P')
   drop procedure P_DWXX_Save
go


if exists (select 1
          from sysobjects
          where name = 'P_DWXX_Del'
          and type = 'P')
   drop procedure P_DWXX_Del
go



CREATE PROCEDURE P_DWXX_Del
    @DWBH Char(3)='',  --分配途径编号
    @nRet Smallint =0 Out  --返回值
--With Encryption
As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_DWXX_Del
    If Exists(Select * From grxxb  Where DWBH=@DWBH) 
      Set @nRet= 1
    If @nRet=0 
        Delete From dwxxb  Where DWBH =@DWBH
    Set @nRet=@@Error
    If @nRet =0 
          Commit Transaction P_DWXX_Del
    Else 
          Rollback Transaction P_DWXX_Del
    Return
--结束
go



CREATE PROCEDURE P_DWXX_Save
    @DWBH Char(3)='',     --单位编号
    @DWMC VarChar(20)='', --单位名称
    @KFTS smallint ='',   --可分套数
    @YFTS smallint ='',   --已分套数
    @nRet Smallint =0 Out --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From dwxxb  Where DWBH=@DWBH)
     Begin
        Set @nRet=1
        Begin Transaction P_DWXX_Save
          Update dwxxb Set DWMC=@DWMC, KFTS=@KFTS, YFTS=@YFTS  Where DWBH=@DWBH
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_DWXX_Save
          Else
            Commit Transaction P_DWXX_Save
      End
    Else
      Begin
        Begin Transaction P_DWXX_Save
          Insert Into dwxxb(DWBH,DWMC,KFTS,YFTS)  Values (@DWBH,@DWMC,@KFTS,@YFTS)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_DWXX_Save
          Else
            Commit Transaction P_DWXX_Save
      End
    Return
--结束
go



CREATE PROCEDURE P_FJFPB_Save
    @FPTJBH Char(2)='',  --分配途径编号
    @YTBH Char(2)='',    --房屋用途编号
    @YJSL smallint=0,    --一居数量
    @EJSL smallint=0,    --二居数量
    @SJSL smallint=0,    --三居数量
    @FSEJ smallint=0,    --复式二居
    @FSSJ smallint=0,    --复式三居
    @QT   smallint=0,    --其他
    @HJ   smallint=0,    --合计
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From fjfpb  Where FPTJBH+YTBH = @FPTJBH+ @YTBH )
     Begin
        Set @nRet=1
        Begin Transaction P_FJFPB_Save
          Update fjfpb Set YJSL=@YJSL,EJSL=@EJSL,SJSL=@SJSL,FSEJ=@FSEJ,FSSJ=@FSSJ,QT=@QT,HJ=@HJ
          Where FPTJBH+YTBH= @FPTJBH+ @YTBH 
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_FJFPB_Save
          Else
            Commit Transaction P_FJFPB_Save
      End
    Else
      Begin
        Begin Transaction P_FJFPB_Save
          Insert Into fjfpb(FPTJBH,YTBH,YJSL,EJSL,SJSL,FSEJ,FSSJ,QT,HJ) 
           Values (@FPTJBH,@YTBH,@YJSL,@EJSL,@SJSL,@FSEJ,@FSSJ,@QT,@HJ)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_FJFPB_Save
          Else
            Commit Transaction P_FJFPB_Save
      End
    Return
--结束
go



CREATE PROCEDURE P_FPZB_Save
    @FPTJBH Char(2)='',  --小区编号
    @FPCS Char(2)='',    --小区名称
    @YJSL smallint=0,    --一居数量
    @EJSL smallint=0,    --二居数量
    @SJSL smallint=0,    --三居数量
    @FSEJ smallint=0,    --复式二居
    @FSSJ smallint=0,    --复式三居
    @QT   smallint=0,    --其他
    @HJ   smallint=0,    --合计
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From fpzb  Where FPTJBH+FPCS= @FPTJBH+ @FPCS )
     Begin
        Set @nRet=1
        Begin Transaction P_FPZB_Save
          Update fpzb Set YJSL=@YJSL,EJSL=@EJSL,SJSL=@SJSL,FSEJ=@FSEJ,FSSJ=@FSSJ,QT=@QT,HJ=@HJ
          Where FPTJBH+FPCS= @FPTJBH+ @FPCS
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_FPZB_Save
          Else
            Commit Transaction P_FPZB_Save
      End
    Else
      Begin
        Begin Transaction P_FPZB_Save
          Insert Into fpzb(FPTJBH,FPCS,YJSL,EJSL,SJSL,FSEJ,FSSJ,QT,HJ) 
           Values (@FPTJBH,@FPCS,@YJSL,@EJSL,@SJSL,@FSEJ,@FSSJ,@QT,@HJ)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_FPZB_Save
          Else
            Commit Transaction P_FPZB_Save
      End
    Return
--结束
go



CREATE PROCEDURE P_GRXX_Del
    @SFZH Char(3)='',  --分配途径编号
    @nRet Smallint =0 Out  --返回值
--With Encryption
As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_GRXX_Del
    If Exists(Select * From zfxxb  Where sfzh=@SFZH) 
      Set @nRet= 1
    If @nRet=0 
        Delete From grxxb  Where SFZH =@SFZH
    Set @nRet=@@Error
    If @nRet =0 
      Commit Transaction P_GRXX_Del
    Else 
      Rollback Transaction P_GRXX_Del
    Return
--结束
go



CREATE PROCEDURE P_GRXX_Save
    @SFZH Char(18)='',     --身份证号
    @XM   VarChar(12)='',  --姓名
    @DWBH Char(3)='',      --单位
    @ZW   VarChar(16)='',  --职务
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From grxxb  Where SFZH=@SFZH)
     Begin
        Set @nRet=1
        Begin Transaction P_GRXX_Save
          Update grxxb Set XM=@XM,DWBH=@DWBH,ZW=@ZW Where SFZH=@SFZH
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_GRXX_Save
          Else
            Commit Transaction P_GRXX_Save
      End
    Else
      Begin
        Begin Transaction P_GRXX_Save
          Insert Into grxxb(SFZH,XM,DWBH,ZW)  Values(@SFZH,@XM,@DWBH,@ZW)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_GRXX_Save
          Else
            Commit Transaction P_GRXX_Save