?? procfindhouse.sql
字號:
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
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -