Alter proc procFindHouse
            @XiaoQuName varchar(20),
            @DongHao  varchar(10),
            @ShiHao varchar(10),
            @Ellect varchar(20) output
As
            Declare @a int,@b int,@c int
 
            Declare @TuDiBianHao varchar(20),@GetWay varchar(10),@AllowFile varchar(20),@FileCode varchar(10),@User varchar(20),@AllowXuKeZhe varchar(20)       
            Declare @TuDiBiaoTuDiBianHao varchar(20)



            Declare @BiaoFangXingBianHao varchar(20)

            Declare @FangXingBianHao varchar(20),@Room varchar(10),@KeTing varchar(10),@WaterRoom varchar(10),@StoreRoom varchar(10),@SunTai varchar(10),@ZhaoXiang varchar(10)
            Declare @JieGei varchar(10),@InMianJi varchar(10),@FengTanMianJi varchar(10),@AllMianJi varchar(10),@JiaGe varchar(10)
 


            Declare @LeiChen varchar(10),@StandUp varchar(10),@UserMan varchar(10),@EndGong varchar(10),@AllowJiGuan varchar(20)


            Declare @DiJiChen varchar(10),@XingZhi varchar(10),@ZhuangDai varchar(10)


            Declare @ZhuangXouBianHao varchar(20) 
            Declare @ZhuangXouBiaoJun varchar(10),@ZhuangXouTiMian varchar(100),@ZhuangXouDoorWindows varchar(100),@ZhuangXouInWall varchar(100),@ZhuangXouTianPeng varchar(100)
            Declare @ZhuangXouChuFan varchar(100),@Tel varchar(100),@Tv varchar(100),@Fire varchar(100)

            
  
      Select @c=count(*) from ShiHao where 小區名稱=@XiaoQuName and 幢號=@DongHao and 室號=@ShiHao
      if @c=0 --表明沒有這個室號
            Select @Ellect='不存在當前室號'
      if @c<>0  --表明有這樣的數據
      Begin
      Select @Ellect='成功'  
            Select @a=count(*) from FindHouse where 小區名稱=@XiaoQuName and 幢號=@DongHao and 室號=@ShiHao
  
        if @a=0 --表示數據庫中沒有這個信息
                    Begin
                      Insert into FindHouse(小區名稱,幢號,室號) values(@XiaoQuName,@DongHao,@ShiHao) 
                      
                      Select @TuDiBiaoTuDiBianHao=土地編號,@LeiChen=樓層數,@StandUp=地上層數,@UserMan=每層戶數,@EndGong=完工狀態,@AllowJiGuan=預售批準機關
                      from DongHao where 小區名稱=@XiaoQuName and 幢號=@DongHao 

                      Select @TuDiBianHao=土地編號,@GetWay=取得方式,@AllowFile=批準文件名,@FileCode=文件號,@User=規化用途,@AllowXuKeZhe=施工許可證號 
                      from TuDiShiYong
                      where 土地編號=@TuDiBiaoTuDiBianHao



                      Select @BiaoFangXingBianHao=房型,@DiJiChen=樓層,@XingZhi=性質,@ZhuangDai=狀態,@ZhuangXouBianHao=裝修標準
                      from ShiHao where 小區名稱=@XiaoQuName and 幢號=@DongHao and 室號=@ShiHao

                      Select @FangXingBianHao=房型編號,@Room=居室,@KeTing=客廳,@WaterRoom=衛生,@StoreRoom=儲藏室,@SunTai=陽臺,@ZhaoXiang=朝向,@JieGei=結構,@InMianJi=預測套內面積,@FengTanMianJi=預測分攤面積,@AllMianJi=預測總計面積,@JiaGe=每平方米樓價
                      from FangXing
                      Where 房型編號=@BiaoFangXingBianHao


                      Select @ZhuangXouBiaoJun=裝修標準,@ZhuangXouTiMian=地面,@ZhuangXouDoorWindows=門窗,@ZhuangXouInWall=內墻,@ZhuangXouTianPeng=天棚,@ZhuangXouChuFan=廚房,@Tel=電話管線,@Tv=電視管線,@Fire=煤氣管線
                      from ZhuangXuo
                      where 編號=@ZhuangXouBianHao

                     --更新數據
                      Update FindHouse set 樓層數=@LeiChen,地上層數=@StandUp,每層戶數=@UserMan,完工狀態=@EndGong,預售批準機關=@AllowJiGuan,土地編號=@TuDiBianHao,取得方式=@GetWay,批準文件名=@AllowFile,文件號=@FileCode,規劃用途=@User,施工許可證號=@AllowXuKeZhe,房型編號=@BiaoFangXingBianHao,樓層=@DiJiChen,性質=@XingZhi,狀態=@ZhuangDai,標準=@ZhuangXouBianHao,居室=@Room,客廳=@KeTing,衛生=@WaterRoom,儲藏室=@StoreRoom,陽臺=@SunTai,朝向=@ZhaoXiang,結構=@JieGei,套內面積=@InMianJi,分攤面積=@FengTanMianJi,總計面積=@AllMianJi,每平方樓價=@JiaGe,地面=@ZhuangXouTiMian,門窗=@ZhuangXouDoorWindows,內墻=@ZhuangXouInWall,天棚=@ZhuangXouTianPeng,廚房=@ZhuangXouChuFan,電話管線=@Tel,電視管線=@Tv,煤氣管線=@Fire
                      where 小區名稱=@XiaoQuName and 幢號=@DongHao and 室號=@ShiHao
                      Select @Ellect='成功'
                      End
                
           if @a<>0 --表示數據庫中有這個信息
                    Begin
                      --Insert into FindHouse(小區名稱,幢號,室號) values(@XiaoQuName,@DongHao,@ShiHao) 
                      
                      Select @TuDiBiaoTuDiBianHao=土地編號,@LeiChen=樓層數,@StandUp=地上層數,@UserMan=每層戶數,@EndGong=完工狀態,@AllowJiGuan=預售批準機關
                      from DongHao where 小區名稱=@XiaoQuName and 幢號=@DongHao 

                      Select @TuDiBianHao=土地編號,@GetWay=取得方式,@AllowFile=批準文件名,@FileCode=文件號,@User=規化用途,@AllowXuKeZhe=施工許可證號 
                      from TuDiShiYong
                      where 土地編號=@TuDiBiaoTuDiBianHao



                      Select @BiaoFangXingBianHao=房型,@DiJiChen=樓層,@XingZhi=性質,@ZhuangDai=狀態,@ZhuangXouBianHao=裝修標準
                      from ShiHao where 小區名稱=@XiaoQuName and 幢號=@DongHao and 室號=@ShiHao

                      Select @FangXingBianHao=房型編號,@Room=居室,@KeTing=客廳,@WaterRoom=衛生,@StoreRoom=儲藏室,@SunTai=陽臺,@ZhaoXiang=朝向,@JieGei=結構,@InMianJi=預測套內面積,@FengTanMianJi=預測分攤面積,@AllMianJi=預測總計面積,@JiaGe=每平方米樓價
                      from FangXing
                      Where 房型編號=@BiaoFangXingBianHao


                      Select @ZhuangXouBiaoJun=裝修標準,@ZhuangXouTiMian=地面,@ZhuangXouDoorWindows=門窗,@ZhuangXouInWall=內墻,@ZhuangXouTianPeng=天棚,@ZhuangXouChuFan=廚房,@Tel=電話管線,@Tv=電視管線,@Fire=煤氣管線
                      from ZhuangXuo
                      where 編號=@ZhuangXouBianHao

                     --更新數據
                      Update FindHouse set 樓層數=@LeiChen,地上層數=@StandUp,每層戶數=@UserMan,完工狀態=@EndGong,預售批準機關=@AllowJiGuan,土地編號=@TuDiBianHao,取得方式=@GetWay,批準文件名=@AllowFile,文件號=@FileCode,規劃用途=@User,施工許可證號=@AllowXuKeZhe,房型編號=@BiaoFangXingBianHao,樓層=@DiJiChen,性質=@XingZhi,狀態=@ZhuangDai,標準=@ZhuangXouBianHao,居室=@Room,客廳=@KeTing,衛生=@WaterRoom,儲藏室=@StoreRoom,陽臺=@SunTai,朝向=@ZhaoXiang,結構=@JieGei,套內面積=@InMianJi,分攤面積=@FengTanMianJi,總計面積=@AllMianJi,每平方樓價=@JiaGe,地面=@ZhuangXouTiMian,門窗=@ZhuangXouDoorWindows,內墻=@ZhuangXouInWall,天棚=@ZhuangXouTianPeng,廚房=@ZhuangXouChuFan,電話管線=@Tel,電視管線=@Tv,煤氣管線=@Fire
                      where 小區名稱=@XiaoQuName and 幢號=@DongHao and 室號=@ShiHao
                      Select @Ellect='成功'
                      End
               End
Go