cretrg.txt

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

      End
    Return
--结束
go



CREATE PROCEDURE P_LYXX_Del
   @LYBH Char(3)='',      --楼宇编号
   @nRet Smallint =0 Out  --返回值
--With Encryption
As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_LYXX_Del
    If Exists(Select * From zfxxb  Where LYBH=@LYBH) 
      Set @nRet= 1
    If @nRet=0 
        Delete From lyxxb  Where LYBH =@LYBH
    Set @nRet=@@Error
    If @nRet =0 
      Commit Transaction P_LYXX_Del
    Else 
      Rollback Transaction P_LYXX_Del
    Return
--结束
go



CREATE PROCEDURE P_LYXX_Save
    @LYBH Char(5)='',   --楼宇编号
    @XQBH Char(3)='',   --小区编号
    @MC   Char(40)='',  --楼宇名称
    @CS   smallint=0 ,  --层数
    @LXBH char(2)='',   --类型编号
    @FJTS smallint=0,   --房间套数
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From lyxxb  Where LYBH= @LYBH )
     Begin
        Set @nRet=1
        Begin Transaction P_Lyxx_Save
          Update lyxxb 
             Set XQBH=@XQBH, MC=@MC, CS=@CS, LXBH=@LXBH, FJTS=@FJTS 
           Where LYBH=@LYBH
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_Lyxx_Save
          Else
            Commit Transaction P_Lyxx_Save
      End
    Else
      Begin
        Begin Transaction P_Lyxx_Save
          Insert Into lyxxb(LYBH,XQBH,MC,CS,LXBH,FJTS)  
              Values (@LYBH,@XQBH,@MC,@CS,@LXBH,@FJTS)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_Lyxx_Save
          Else
            Commit Transaction P_Lyxx_Save
      End
    Return
--结束
go



CREATE PROCEDURE P_SYS0_Del
    @BH Char(2)='',  --分配途径编号
    @nRet Smallint =0 Out  --返回值
--With Encryption
As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_SYS0_Del
    If Exists(Select * From fpzb  Where fptjbh=@BH) or 
       Exists(Select * From fjfpb  Where fptjbh=@BH)
    Set @nRet= 1
    If @nRet=0 
        Delete From fptjb  Where bh =@BH
    Set @nRet=@@Error
    If @nRet =0 
          Commit Transaction P_SYS0_Del
    Else 
          Rollback Transaction P_SYS0_Del
    Return
--结束
go



CREATE PROCEDURE P_SYS0_Save
    @BH Char(2)='',  --编号
    @MC Char(12)='',  --名称
    @BZ Char(20)='',  --备注
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From fptjb  Where BH=@BH)
     Begin
        Set @nRet=1
        Begin Transaction P_SYS0_Save
          Update fptjb Set MC=@MC, bz=@BZ   Where BH=@BH
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_SYS0_Save
          Else
            Commit Transaction P_SYS0_Save
      End
    Else
      Begin
        Begin Transaction P_SYS0_Save
          Insert Into fptjb(BH,MC,BZ)  Values (@BH,@MC,@BZ)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_SYS0_Save
          Else
            Commit Transaction P_SYS0_Save
      End
    Return
--结束
go



CREATE PROCEDURE P_SYS1_Del
    @BH Char(2)='',  --房屋用途编号
    @nRet Smallint =0 Out  --返回值
--With Encryption
As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_SYS1_Del
    If Exists(Select * From fjfpb Where ytbh=@BH) or 
       Exists(Select * From zgfpb Where ytbh=@BH) or
       Exists(Select * From zfxxb Where ytbh=@BH)
      Set @nRet= 5
    If @nRet=0 
       Delete From fwytb  Where bh =@BH
    Set @nRet=@@Error
    If @nRet =0 
       Commit Transaction P_SYS1_Del
    Else 
       Rollback Transaction P_SYS1_Del
    Return
--结束
go



CREATE PROCEDURE P_SYS1_Save
    @BH Char(2)='',  --编号
    @MC Char(12)='',  --名称
    @BZ Char(20)='',  --备注
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From fwytb  Where BH=@BH)
     Begin
        Set @nRet=1
        Begin Transaction P_SYS1_Save
          Update fwytb Set MC=@MC, bz=@BZ   Where BH=@BH
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_SYS1_Save
          Else
            Commit Transaction P_SYS1_Save
      End
    Else
      Begin
        Begin Transaction P_SYS1_Save
          Insert Into fwytb(BH,MC,BZ)  Values (@BH,@MC,@BZ)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_SYS1_Save
          Else
            Commit Transaction P_SYS1_Save
      End
    Return
--结束
go



CREATE PROCEDURE P_SYS2_Del
    @BH Char(2)='',  --房屋户型编号
    @nRet Smallint =0 Out  --返回值
--With Encryption
As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_SYS2_Del
    If Exists(Select * From zfxxb Where hxbh=@BH) 
      Set @nRet= 5
    If @nRet=0 
       Delete From fwhxb  Where bh =@BH
    Set @nRet=@@Error
    If @nRet =0 
       Commit Transaction P_SYS2_Del
    Else 
       Rollback Transaction P_SYS2_Del
    Return
--结束
go



CREATE PROCEDURE P_SYS2_Save
    @BH Char(2)='',  --编号
    @MC Char(12)='',  --名称
    @BZ Char(20)='',  --备注
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From fwhxb  Where BH=@BH)
     Begin
        Set @nRet=1
        Begin Transaction P_SYS2_Save
          Update fwhxb Set MC=@MC, bz=@BZ   Where BH=@BH
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_SYS2_Save
          Else
            Commit Transaction P_SYS2_Save
      End
    Else
      Begin
        Begin Transaction P_SYS2_Save
          Insert Into fwhxb(BH,MC,BZ)  Values (@BH,@MC,@BZ)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_SYS2_Save
          Else
            Commit Transaction P_SYS2_Save
      End
    Return
--结束
go



CREATE PROCEDURE P_SYS3_Del
    @BH Char(2)='',  --房屋户型编号
    @nRet Smallint =0 Out  --返回值
--With Encryption
As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_SYS3_Del
    If Exists(Select * From zfxxb Where cxbh=@BH) 
      Set @nRet= 5
    If @nRet=0 
       Delete From fwcxb  Where bh =@BH
    Set @nRet=@@Error
    If @nRet =0 
       Commit Transaction P_SYS3_Del
    Else 
       Rollback Transaction P_SYS3_Del
    Return
--结束
go



CREATE PROCEDURE P_SYS3_Save
    @BH Char(2)='',  --编号
    @MC Char(12)='',  --名称
    @BZ Char(20)='',  --备注
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From fwcxb  Where BH=@BH)
     Begin
        Set @nRet=1
        Begin Transaction P_SYS3_Save
          Update fwcxb Set MC=@MC, bz=@BZ   Where BH=@BH
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_SYS3_Save
          Else
            Commit Transaction P_SYS3_Save
      End
    Else
      Begin
        Begin Transaction P_SYS3_Save
          Insert Into fwcxb(BH,MC,BZ)  Values (@BH,@MC,@BZ)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_SYS3_Save
          Else
            Commit Transaction P_SYS3_Save
      End
    Return
--结束
go



CREATE PROCEDURE P_SYS4_Del
    @BH Char(2)='',  --房屋户型编号
    @nRet Smallint =0 Out  --返回值
--With Encryption
As
    Set NoCount On
    Set @nRet=0
    Begin Transaction P_SYS4_Del
    If Exists(Select * From zfxxb Where djbh=@BH) 
      Set @nRet= 5
    If @nRet=0 
       Delete From fwdjb  Where bh =@BH
    Set @nRet=@@Error
    If @nRet =0 
       Commit Transaction P_SYS4_Del
    Else 
       Rollback Transaction P_SYS4_Del
    Return
--结束
go



CREATE PROCEDURE P_SYS4_Save
    @BH Char(2)='',  --编号
    @MC Char(12)='',  --名称
    @BZ Char(20)='',  --备注
    @nRet Smallint =0 Out  --返回值

--With Encryption
As
   Set NoCount On
   If Exists(Select * From fwdjb  Where BH=@BH)
     Begin
        Set @nRet=1
        Begin Transaction P_SYS4_Save
          Update fwdjb Set MC=@MC, bz=@BZ   Where BH=@BH
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_SYS4_Save
          Else
            Commit Transaction P_SYS4_Save
      End
    Else
      Begin
        Begin Transaction P_SYS4_Save
          Insert Into fwdjb(BH,MC,BZ)  Values (@BH,@MC,@BZ)
          Set @nRet=@@Error
          If @nRet <>0 
            Rollback Transaction P_SYS4_Save
          Else
            Commit Transaction P_SYS4_Save
      End
    Return
--结束
go





CREATE PROCEDURE P_TXFP_Save
    @ZFBH Char(10)='',  --住房编号
    @DWBH Char(3)='',  --单位编号
    @MSG  Varchar(50)='' out,
    @nRet Smallint =0 Out  --返回值 1-单位置空 2-分配未超规定 3-相等 4-超出 5-住房号不存在 7-房屋不可分 8-户型不确定 9-更新出错

AS
  Set NoCount On
  declare @CompA smallint, @CompB smallint, @hx varchar(4)
   If Exists(Select * From zfxxb  Where ZFBH=@ZFBH)
     Begin
        Set @nRet=1
        IF @DWBH=''
         begin
          Update zfxxb Set DWBH=NULL,sffp=0 Where ZFBH=@ZFBH
          If @@Error <>0
               Set @nRet=9
         end  
        ELSE
          begin
            select @hx= hxbh from zfxxb where zfbh=@ZFBH
            select @CompA=count(*) from zfxxb where hxbh= @hx and dwbh=@dwbh
            set @compB=-1
            if @hx= '01'
              begin
                select @CompB=sum(yjsl) from zgfpb where dwbh=@dwbh
                if @compB is null 
                  set    @compb =0
                set @Msg='一居室'
              end
            else
            if @hx= '02'
              begin
                select @CompB=sum(ejsl) from zgfpb where dwbh=@dwbh
                if @compB is null 
                  set    @compb =0
                set @Msg='二居室'
              end
            else
            if @hx= '03'
              begin
                select @CompB=sum(sjsl) from zgfpb where dwbh=@dwbh
                if @compB is null 
                  set    @compb =0
                set @Msg='三居室'
              end
            else
            if @hx= '04'
              begin
                select @CompB=sum(fsej) from zgfpb where dwbh=@dwbh
                if @compB is null 
                  set    @compb =0
                set @Msg='复式二居'
              end
            else
            if @hx= '05'
              begin
                select @CompB=sum(fssj) from zgfpb where dwbh=@dwbh
                if @compB is null 
                  set    @compb =0
                set @Msg='复式三居'
              end
            else
              set @nret= 8  
            
            if @compB <>-1 
            begin 
              if @CompA< @CompB 
              begin
                select @nRet= kffp from zfxxb where ZFBH=@ZFBH
                if @nRet= 1 
                begin
                  set @nRet= 2
                  Update zfxxb Set DWBH=@DWBH,sffp=1 Where ZFBH=@ZFBH
                  set @MSG=@MSG+'  可分<'+convert(varchar(4),@compB)+'>套，已分<'+convert(varchar(4),@compA+1)+'>套，剩余<'+convert(varchar(4),@compB-@compA-1)+'>套'
                  If @@Error <>0
                      Set @nRet=9
                end
                else
                  select @nRet=7
              end
              else
              if @CompA= @CompB
              begin
                set @nRet= 3
                set @MSG=@MSG+'  可分<'+convert(varchar(4),@compB)+'>套，已分<'+convert(varchar(4),@compA)+'>套'
              end
              else
              begin
                set @nret= 4
                set @MSG=@MSG+'  可分<'+convert(varchar(4),@compB)+'>套，已分<'+convert(varchar(4),@compA)+'>套,请重新设置分配数量'
              end
            end
          end
     End
    else
       set @nret=5

    Return

--结束

GO



CREATE PROCEDURE P_Test  
      @nret smallint=0 out
AS
  Set NoCount On
  declare @yjsl smallint,@ejsl smallint,@sjsl smallint,@fsej smallint,@fssj smallint,@qt smallint,@hj smallint
  declare @yjsl1 smallint,@ejsl1 smallint,@sjsl1 smallint,@fsej1 smallint,@fssj1 smallint,@qt1 smallint,@hj1 smallint
  select @yjsl= sum(yjsl),
         @ejsl= sum(ejsl),
         @sjsl= sum(sjsl),
         @fsej= sum(fsej),
         @fssj= sum(fssj),
         @qt=   sum(qt),
         @hj=   sum(hj)
   from fpzb 
   where fptjbh<> '88'
  select @yjsl1= yjsl,
         @ejsl1= ejsl,
         @sjsl1= sjsl,
         @fsej1= fsej,
         @fssj1= fssj,
         @qt1=   qt,
         @hj1=   hj
   from fpzb 
   where fptjbh= '88'
   if @yjsl<> @yjsl1 
      set @nret=1
   else 
   if @ejsl<> @ejsl1
      set @nret=2
   else 
   if @sjsl<> @sjsl1
      set @nret=3
   else 
   if @fsej<> @fsej1
      set @nret=4
   else 
   if @fssj<> @fssj1
      set @nret=5
   else 
   if @qt<> @qt1
      set @nret=6
   else 
   if @hj<> @hj1