sql增删改查存储过程精Word下载.docx
《sql增删改查存储过程精Word下载.docx》由会员分享，可在线阅读，更多相关《sql增删改查存储过程精Word下载.docx（29页珍藏版）》请在冰点文库上搜索。
set@employee_ID='
emp1001'
--作动见编号就知道是什么表
else
emp'
+cast（cast（substring（@employee_ID,4,4asint+1asvarchar（20
insertintotb_employeevalues（@employee_ID,@employee_name,@employee_sex,
@employee_birthday,@employee_phone,@employee_cardID,
@employee_address,@gov_id,@employee_study,@employee_basepay
End
CREATEprocproc_employee_update
updatetb_employeesetemployee_name=@employee_name,
employee_sex=@employee_sex,
employee_birthday=@employee_birthday,
employee_phone=@employee_phone,
employee_cardID=@employee_cardID,
employee_address=@employee_address,
gov_id=@gov_id,
employee_basepay=@employee_basepay,
employee_study=@employee_study
whereemployee_ID=@employee_ID
GO
createprocproc_favor_delete
@house_favorIDvarchar（10=null,
@proc_infovarchar（20output
deletefromtb_favorwherehouse_favorID=@house_favorID;
if（@@error=0
set@proc_info='
OK'
systemerror:
'
+cast（@@errorasvarchar（6
createprocproc_favor_insert
@favor_namevarchar（20='
@favor_remarkvarchar（50='
select@house_favorID=Max（house_favorIDfromtb_favor
if（@house_favorIDisnull
set@house_favorID='
fav1001'
fav'
+cast（cast（substring（@house_favorID,4,4asint+1asvarchar（20
ifexists（selectfavor_namefromtb_favorwherefavor_name=@favor_name
isHave'
--控制不要输入两个同样的信息
insertintotb_favorvalues（@house_favorID,@favor_name,@favor_remark
ok'
end
createprocproc_favor_update
@house_favorIDvarchar（10,
updatetb_favorsetfavor_name=@favor_name,favor_remark=@favor_remarkwherehouse_favorID=@house_favorID
createprocproc_fitment_delete
@house_fitmentIDvarchar（10=null
deletefromtb_fitmentwherehouse_fitmentID=@house_fitmentID
CREATEprocproc_fitment_insert
@house_fitmentIDvarchar（10=null,
@fitment_namevarchar（20='
@fitment_remarkvarchar（50='
select@house_fitmentID=Max（house_fitmentIDfromtb_fitment
print@house_fitmentID
if（@house_fitmentIDisnull
set@house_fitmentID='
fit1001'
fit'
+cast（cast（substring（@house_fitmentID,4,4asint+1asvarchar（20
ifexists（selectfitment_namefromtb_fitmentwherefitment_name=@fitment_name
insertintotb_fitmentvalues（@house_fitmentID,@fitment_name,@fitment_remark
createprocproc_fitment_update
updatetb_fitmentsetfitment_name=@fitment_name,fitment_remark=@fitment_remarkwherehouse_fitmentID=@house_fitmentID
createprocproc_floor_delete
@house_floorIDvarchar（10=null
deletefromtb_floorwherehouse_floorID=@house_floorID
CREATEprocproc_floor_insert
@house_floorIDvarchar（10=null,
@floor_namevarchar（20='
@floor_remarkvarchar（50='
select@house_floorID=Max（house_floorIDfromtb_floor
if（@house_floorIDisnull
set@house_floorID='
flo1001'
flo'
+cast（cast（substring（@house_floorID,4,4asint+1asvarchar（20
ifexists（selectfloor_namefromtb_floorwherefloor_name=@floor_name
insertintotb_floorvalues（@house_floorID,@floor_name,@floor_remark
createprocproc_floor_update
updatetb_floorsetfloor_name=@floor_name,
floor_remark=@floor_remark
wherehouse_floorID=@house_floorID
createprocproc_gov_delete
@gov_idvarchar（10=null
deletefromtb_govwheregov_id=@gov_id
CREATEprocproc_gov_insert
@gov_idvarchar（10=null,
@gov_namevarchar（20='
@gov_remarkvarchar（50='
select@gov_id=Max（gov_idfromtb_gov
if（@gov_idisnull
set@gov_id='
gov1001'
gov'
+cast（cast（substring（@gov_id,4,4asint+1asvarchar（20
ifexists（selectgov_namefromtb_govwheregov_name=@gov_name
insertintotb_govvalues（@gov_id,@gov_name,@gov_remark
createprocproc_gov_update
as
updatetb_govsetgov_name=@gov_name,
gov_remark=@gov_remark
wheregov_id=@gov_id
createprocproc_house_delete
@house_IDvarchar（10
deletefromtb_housewherehouse_ID=@house_ID
CREATEprocproc_house_insert
@house_IDvarchar（10=null,
@house_companyNamevarchar（50,
@huose_typeIDvarchar（10,
@house_seatIDvarchar（10,
@house_statevarchar（10,
@house_fitmentIDvarchar（10,
@house_favorIDvarchar（10,
@house_mothedIDvarchar（10,
@huose_mapvarchar（50,
@house_pricefloat,
@house_floorIDvarchar（10,
@house_buildYearint,
@house_areavarchar（20,
@house_remarkvarchar（50,
@user_idsvarchar（10
set@house_ID=（selectMax（house_IDfromtb_house
declare@sqlvarchar（300
if（@house_IDisnull
set@house_ID='
hou1001'
hou'
+cast（substring（@house_ID,4,4+1asvarchar（10
insertintotb_housevalues
（@house_ID,
@house_companyName,
@huose_typeID,
@house_seatID,
@house_state,
@house_fitmentID,
@house_favorID,
@house_mothedID,
@huose_map,
@house_price,
@house_floorID,
@house_buildYear,
@house_area,
@house_remark,
@user_ids
--上面先插入
set@sql='
selectuser_id用户编号,house_price房价,house_area房屋面积fromtb_intentwhere
huose_typeID='
+@huose_typeID+'
andhouse_seatID='
+@house_seatID+'
andhouse_fitmentID='
+@house_fitmentID+'
andhouse_floorID='
+@house_floorID+'
andhouse_favorID='
+@house_favorID+'
andhouse_mothedID='
+@house_mothedID+'
print@sql
exec（@sql
CREATEprocproc_house_update
@house_IDvarchar（10,
@house_buildYearvarchar（10,
@house_remarkvarchar（50
updatetb_houseset
house_companyName=@house_companyName,
huose_typeID=@huose_typeID,
house_seatID=@house_seatID,
house_fitmentID=@house_fitmentID,
house_favorID=@house_favorID,
house_mothedID=@house_mothedID,
huose_map=@huose_map,
house_price=@house_price,
house_floorID=@house_floorID,
house_buildYear=@house_buildYear,
house_area=@house_area,
house_remark=@house_remark
wherehouse_ID=@house_ID
CREATEprocproc_intent_insert
@intend_IDvarchar（10=null,
@user_idvarchar（10,
@house_fitmentIDvarchar（10,
@house_pricenumeric（10,
@house_areavarchar（20
set@intend_ID=（selectMax（intent_IDfromtb_intent
if（@intend_IDisnull
set@intend_ID='
int1001'
int'
+cast（substring（@intend_ID,4,4+1asvarchar（10
insertintotb_intentvalues
（
@intend_ID,
@user_id,
@house_area
selecthouse_id房屋编号,user_ids户主编号,house_price价格,house_area房屋面积fromtb_housewhere
createprocproc_login_delete
@login_namevarchar（20=null,
@ReturnInfonvarchar（50=nulloutput
--删除时给你两种方法员工编号和用户名
if（@employee_IDisnulland@employee_IDisnull
set@ReturnInfo='
xingxibuquan'
deletefromtb_loginwhereemployee_ID=@employee_IDorlogin_name=@login_name
systeminfo'
+cast（@@errorasvarchar（10
createprocproc_login_insert
@login_idvarchar（10=null,
@login_pwdvarchar（15=null,
@login_powervarchar（10=null,
a
升级会员 