procfindhouse.sql

扬州升瑞售楼系统2000扬州升瑞售楼系统2000扬州升瑞售楼系统2000扬州升瑞售楼系统2000扬州升瑞售楼系统2000

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