cretrg.txt

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

      set @nret=7

    Return
--结束
go



CREATE procedure P_TestCX
   @LYBH char(5)='',     --楼宇编号
   @BH char(2)='',        -- 房屋编号后两位
   @cxmc varchar(12) out, --朝向名称
   @nRet smallint=0 out   -- 返回值
as
  set nocount on
  set @nRet=1
    begin tran 
      select zfbh,lybh,hxbh,cxbh,dwbh  into #temp_T from zfxxb where lybh=@LYBH
      select cxbh,cnt=count(*),bh=substring(zfbh,9,2) into #temp_table from #temp_T 
         group by cxbh,substring(zfbh,9,2)
      select bh,cnt=max(cnt) into #temp1 from #temp_table
         group by bh
      select  @cxmc= c.mc  from #temp_table a,#temp1 b,fwcxb c
      where a.cnt=b.cnt and a.bh=b.bh and a.cxbh=c.bh and b.bh =@BH
      
      drop table #temp_T    
      drop table #temp_table
      drop table #temp1
    Set @nRet=@@Error
    If @nRet <>0 
      Rollback Tran
    Else
      Commit Tran
 
   return
go



CREATE PROCEDURE P_TestFJ  
      @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 fjfpb 
   where ytbh<> '99'
  select @yjsl1= yjsl,
         @ejsl1= ejsl,
         @sjsl1= sjsl,
         @fsej1= fsej,
         @fssj1= fssj,
         @qt1=   qt,
         @hj1=   hj
   from fjfpb 
   where ytbh= '99'
   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
      set @nret=7

    Return
--结束
go



CREATE procedure P_TestHX
   @LYBH char(5)='',     --楼宇编号
   @BH char(2)='',        -- 房屋编号后两位
   @hxmc varchar(12) out, --户型名称
   @nRet smallint=0 out   -- 返回值
as
  set nocount on
  set @nRet=1
    begin tran 
      select zfbh,lybh,hxbh,cxbh,dwbh  into #temp_T from zfxxb where lybh=@LYBH
      select hxbh,cnt=count(*),bh=substring(zfbh,9,2) into #temp_table from #temp_T 
         group by hxbh,substring(zfbh,9,2)
      select bh,cnt=max(cnt) into #temp1 from #temp_table
         group by bh
      select  @hxmc= c.mc  from #temp_table a,#temp1 b,fwhxb c
      where a.cnt=b.cnt and a.bh=b.bh and a.hxbh=c.bh and b.bh =@BH

      drop table #temp_T
      drop table #temp_table
      drop table #temp1
    Set @nRet=@@Error
    If @nRet <>0 
      Rollback Tran
    Else
      Commit Tran
 
   return
go



CREATE PROCEDURE P_TestYTB
   @nCompA smallint=0 out,  --分局分配表合计
   @nCompB smallint=0 out,  --用途分配表合计
   @nret smallint=0 out
AS
  Set NoCount On
  declare @zgfp smallint,  --职工分配合计
          @jlfp smallint,  --奖励分配合计
          @cqfp smallint,  --拆迁分配合计
          @ldgb smallint,  --领导干部合计
          @shyf smallint,  --社会用房合计
          @gyfw smallint,  --公用房屋合计
          @jdfw smallint,  --机动房屋合计
          @qtyf smallint   --其他用房合计
  declare @zgfp1 smallint,
          @jlfp1 smallint,
          @cqfp1 smallint,
          @ldgb1 smallint,
          @shyf1 smallint,
          @gyfw1 smallint,
          @jdfw1 smallint,
          @qtyf1 smallint
  select @zgfp1= sum(zgfp),
         @jlfp1= sum(jlfp),
         @cqfp1= sum(cqfp),
         @ldgb1= sum(ldgb),
         @shyf1= sum(shyf),
         @gyfw1= sum(gyfw),
         @jdfw1= sum(jdfw),
         @qtyf1= sum(qtyf)
    from ytfpb 
  select @zgfp= hj from fjfpb where ytbh='01'
  select @jlfp= hj from fjfpb where ytbh='02'
  select @cqfp= hj from fjfpb where ytbh='03'
  select @ldgb= hj from fjfpb where ytbh='04'
  select @shyf= hj from fjfpb where ytbh='05'
  select @gyfw= hj from fjfpb where ytbh='06'
  select @jdfw= hj from fjfpb where ytbh='07'
  select @qtyf= hj from fjfpb where ytbh='08'
   if @zgfp<> @zgfp1
     begin 
       set @nret=1
       set @nCompA= @zgfp
       set @nCompB= @zgfp1
     end
   else 
   if @jlfp<> @jlfp1
     begin 
      set @nret=2
      set @nCompA= @jlfp
      set @nCompB= @jlfp1
     end
   else 
   if @cqfp<> @cqfp1
     begin 
      set @nret=3
      set @nCompA= @cqfp
      set @nCompB= @cqfp1
     end
   else 
   if @ldgb<> @ldgb1
     begin 
      set @nret=4
      set @nCompA= @ldgb
      set @nCompB= @ldgb1
     end
   else 
   if @shyf<> @shyf1
     begin 
      set @nret=5
      set @nCompA= @shyf
      set @nCompB= @shyf1
     end
   else 
   if @gyfw<> @gyfw1
     begin 
      set @nret=6
      set @nCompA= @gyfw
      set @nCompB= @gyfw1
     end
   else 
   if @jdfw<> @jdfw1
     begin 
      set @nret=7
      set @nCompA= @jdfw
      set @nCompB= @jdfw1
     end
   else
   if @qtyf<> @qtyf1
     begin 
      set @nret=8
      set @nCompA= @qtyf
      set @nCompB= @qtyf1
     end

    Return
--结束
go



CREATE PROCEDURE P_TestZFXXB
   @nCompA smallint=0 out,  --职工分配表各单位户型统计值
   @nCompB smallint=0 out,  --住房信息表各单位户型统计值
   @nhx smallint=0 out,
   @DWBH char(3)='' 
AS
  Set NoCount On
  declare @yjsl smallint,  --一居数量
          @ejsl smallint,  --二居数量
          @sjsl smallint,  --三居数量
          @fsej smallint,  --复式二居
          @fssj smallint,  --复式三居
          @qt   smallint,  --其它
          @yjsl1 smallint,  --一居数量
          @ejsl1 smallint,  --二居数量
          @sjsl1 smallint,  --三居数量
          @fsej1 smallint,  --复式二居
          @fssj1 smallint,  --复式三居
          @qt1   smallint,  --其它
          @i     smallint   --计数器 
     set @nhx = 0
     select @yjsl1= count(*) from zfxxb where dwbh= @dwbh and hxbh='01'
     select @ejsl1= count(*) from zfxxb where dwbh= @dwbh and hxbh='02'
     select @sjsl1= count(*) from zfxxb where dwbh= @dwbh and hxbh='03'
     select @fsej1= count(*) from zfxxb where dwbh= @dwbh and hxbh='04'
     select @fssj1= count(*) from zfxxb where dwbh= @dwbh and hxbh='05'
     select @qt1 =  count(*) from zfxxb where dwbh= @dwbh and (hxbh='' or hxbh is null)
     select @yjsl= sum(yjsl),@ejsl=sum(ejsl),@sjsl=sum(sjsl),@fsej=sum(fsej),@fssj=sum(fssj),@qt=sum(qt)
      from zgfpb
      where dwbh= @dwbh 
     if @yjsl < @yjsl1
       begin 
         set @nhx=1
         set @nCompA= @yjsl
         set @nCompB= @yjsl1
       end
     else 
     if @ejsl< @ejsl1
       begin 
        set @nhx=2
        set @nCompA= @ejsl
        set @nCompB= @ejsl1
       end
     else 
     if @sjsl< @sjsl1
       begin 
         set @nhx=3
         set @nCompA= @sjsl
         set @nCompB= @sjsl1
       end
     else 
     if @fsej< @fsej1
       begin 
        set @nhx=4
        set @nCompA= @fsej
        set @nCompB= @fsej1
     end
     else 
     if @fssj< @fssj1
       begin 
        set @nhx=5
        set @nCompA= @fssj
        set @nCompB= @fssj1
       end
     else 
     if @qt< @qt1
       begin 
        set @nhx=6
        set @nCompA= @qt
        set @nCompB= @qt1
       end

    Return
--结束
go



CREATE PROCEDURE P_TestZG0
   @nCompA smallint=0 out,  --分局分配表交叉值
   @nCompB smallint=0 out,  --职工分配表所有单位户型与用途交叉统计值
   @nhx smallint=0 out,
   @cyt varchar(12)='' out
AS
  Set NoCount On
  declare @yjsl smallint,  --一居数量
          @ejsl smallint,  --二居数量
          @sjsl smallint,  --三居数量
          @fsej smallint,  --复式二居
          @fssj smallint,  --复式三居
          @qt   smallint,  --其它
          @yjsl1 smallint,  --一居数量
          @ejsl1 smallint,  --二居数量
          @sjsl1 smallint,  --三居数量
          @fsej1 smallint,  --复式二居
          @fssj1 smallint,  --复式三居
          @qt1   smallint,  --其它
          @i     smallint   --计数器 
  set @i = 1
  set @nhx = 0
  while @i < 9 
   begin
     select @yjsl1=coalesce(sum(yjsl),0),@ejsl1=coalesce(sum(ejsl),0),@sjsl1=coalesce(sum(sjsl),0),
            @fsej1=coalesce(sum(fsej),0),@fssj1=coalesce(sum(fssj),0),@qt1=coalesce(sum(qt),0)
      from zgfpb
      where ytbh='0'+convert(char(1),@i) 
     select @yjsl= yjsl,@ejsl=ejsl,@sjsl= sjsl,@fsej= fsej,@fssj= fssj,@qt= qt
      from fjfpb
      where ytbh='0'+convert(char(1),@i) 
     select @cyt= mc from fwytb where bh='0'+convert(char(1),@i)
     if @yjsl<> @yjsl1
       begin 
         set @nhx=1
         set @nCompA= @yjsl
         set @nCompB= @yjsl1
       end
     else 
     if @ejsl<> @ejsl1
       begin 
        set @nhx=2
        set @nCompA= @ejsl
        set @nCompB= @ejsl1
       end
     else 
     if @sjsl<> @sjsl1
       begin 
         set @nhx=3
         set @nCompA= @sjsl
         set @nCompB= @sjsl1
       end
     else 
     if @fsej<> @fsej1
       begin 
        set @nhx=4
        set @nCompA= @fsej
        set @nCompB= @fsej1
     end
     else 
     if @fssj<> @fssj1
       begin 
        set @nhx=5
        set @nCompA= @fssj
        set @nCompB= @fssj1
       end
     else 
     if @qt<> @qt1
       begin 
        set @nhx=6
        set @nCompA= @qt
        set @nCompB= @qt1
       end
     if @nhx<>0 
        break
     else
       begin
         set @i= @i+1
         continue
       end

   end

    Return
--结束
go



CREATE PROCEDURE P_TestZG1
   @nCompA smallint=0 out,  --用途分配表交叉值
   @nCompB smallint=0 out,  --职工分配表每单位各用途合计值 
   @cdw varchar(20)='' out,    --数量不一致单位
   @nyt smallint=0 out      --出错用途标记
AS
  Set NoCount On
  declare @dwbh char(3),@zgfp smallint,@jlfp smallint,@cqfp smallint,@ldgb smallint,
          @shyf smallint,@gyfw smallint,@jdfw smallint,@qtyf smallint,
          @zgfp1 smallint,@jlfp1 smallint,@cqfp1 smallint,@ldgb1 smallint,
          @shyf1 smallint,@gyfw1 smallint,@jdfw1 smallint,@qtyf1 smallint
  set @nyt = 0

  DECLARE yt_cursor CURSOR FOR
   SELECT dwbh,zgfp,jlfp,cqfp,ldgb,shyf,gyfw,jdfw,qtyf FROM ytfpb
  OPEN yt_cursor
  FETCH NEXT FROM yt_cursor
  INTO @dwbh, @zgfp,@jlfp,@cqfp,@ldgb,@shyf,@gyfw,@jdfw,@qtyf

  while @@FETCH_STATUS = 0
  begin
     if not exists(select * from zgfpb where ytbh='01' and dwbh=@dwbh )
        select @zgfp1=0
     else
        select @zgfp1=hj from zgfpb where ytbh='01' and dwbh= @dwbh
     if not exists(select * from zgfpb where ytbh='02' and dwbh=@dwbh )
        select @jlfp1=0
     else
        select @jlfp1=hj from zgfpb where ytbh='02' and dwbh= @dwbh
     if not exists(select * from zgfpb where ytbh='03' and dwbh=@dwbh )
        select @cqfp1=0
     else
       select @cqfp1=hj from zgfpb where ytbh='03' and dwbh= @dwbh
     if not exists(select * from zgfpb where ytbh='04' and dwbh=@dwbh )
        select @ldgb1=0
     else
        select @ldgb1=hj from zgfpb where ytbh='04' and dwbh= @dwbh
     if not exists(select * from zgfpb where ytbh='05' and dwbh=@dwbh )
        select @shyf1=0
     else
        select @shyf1=hj from zgfpb where ytbh='05' and dwbh= @dwbh
     if not exists(select * from zgfpb where ytbh='06' and dwbh=@dwbh )
        select @gyfw1=0
     else
        select @gyfw1=hj from zgfpb where ytbh='06' and dwbh= @dwbh
     if not exists(select * from zgfpb where ytbh='07' and dwbh=@dwbh )
        select @jdfw1=0
     else
        select @jdfw1=hj from zgfpb where ytbh='07' and dwbh= @dwbh
     if not exists(select * from zgfpb where ytbh='08' and dwbh=@dwbh )
        select @qtyf1=0
     else
        select @qtyf1=hj from zgfpb where ytbh='08' and dwbh= @dwbh 

     select @cdw= dwmc from dwxxb where dwbh=@dwbh
     if @zgfp<> @zgfp1
       begin 
         set @nyt=1  
         set @nCompA= @zgfp
         set @nCompB= @zgfp1
       end
     else 
     if @jlfp<> @jlfp1
       begin 
        set @nyt=2  
        set @nCompA= @jlfp
        set @nCompB= @jlfp1
       end
     else 
     if @cqfp<> @cqfp1
       begin 
         set @nyt=3  
         set @nCompA= @cqfp
         set @nCompB= @cqfp1
       end
     else 
     if @ldgb<> @ldgb1
       begin 
        set @nyt=4  
        set @nCompA= @ldgb
        set @nCompB= @ldgb1
     end
     else 
     if @shyf<> @shyf1
       begin 
        set @nyt=5  
        set @nCompA= @shyf
        set @nCompB= @shyf1
       end
     else 
     if @gyfw<> @gyfw1
       begin 
        set @nyt=6  
        set @nCompA= @gyfw
        set @nCompB= @gyfw1
       end
     else 
     if @jdfw<> @jdfw1
       begin 
        set @nyt=7  
        set @nCompA= @jdfw
        set @nCompB= @jdfw1
       end
     else 
     if @qtyf<> @qtyf1
       begin 
        set @nyt=8  
        set @nCompA= @qtyf
        set @nCompB= @qtyf1
       end
    
     if @nyt<>0 
        break
     else
       FETCH NEXT FROM yt_cursor
       INTO @dwbh, @zgfp,@jlfp,@cqfp,@ldgb,@shyf,@gyfw,@jdfw,@qtyf
  end

  CLOSE yt_cursor 
  DEALLOCATE yt_cursor


    Return
--结束
go



CREATE PROCEDURE P_UserDel
    @userid Char(3)='',  --房屋户型编号
    @nRet Smallint =0 Out  --返回值
--With Encryption