sql增删改查存储过程精.docx
《sql增删改查存储过程精.docx》由会员分享，可在线阅读，更多相关《sql增删改查存储过程精.docx（29页珍藏版）》请在冰豆网上搜索。
sql增删改查存储过程精
CREATEprocproc_clear
as
begin
deletefromtb_userwhereuser_idsnotin（selectuser_idsfromtb_houseanduser_type='lend'
end
GO
createprocproc_employee_delete
@employee_IDvarchar（10
as
begin
deletefromtb_employeewhereemployee_ID=@employee_ID
end
GO
CREATEprocproc_employee_insert
@employee_IDvarchar（10=null,
@employee_namevarchar（20=null,
@employee_sexvarchar（10=null,
@employee_birthdaydatetime,
@employee_phonevarchar（20,
@employee_cardIDvarchar（20,
@employee_addressvarchar（50,
@gov_idvarchar（16,
@employee_studyvarchar（16,
@employee_basepaynumeric
as
begin
select@employee_ID=Max（employee_IDfromtb_employee
if（@employee_IDisnull
set@employee_ID='emp1001'--作动见编号就知道是什么表
else
set@employee_ID='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
GO
CREATEprocproc_employee_update
@employee_IDvarchar（10=null,
@employee_namevarchar（20=null,
@employee_sexvarchar（10=null,
@employee_birthdaydatetime,
@employee_phonevarchar（20,
@employee_cardIDvarchar（20,
@employee_addressvarchar（50,
@gov_idvarchar（16,
@employee_studyvarchar（16,
@employee_basepaynumeric
as
begin
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
end
GO
createprocproc_favor_delete
@house_favorIDvarchar（10=null,
@proc_infovarchar（20output
as
begin
deletefromtb_favorwherehouse_favorID=@house_favorID;
if（@@error=0
set@proc_info='OK'
else
set@proc_info='systemerror:
'+cast（@@errorasvarchar（6
end
GO
createprocproc_favor_insert
@house_favorIDvarchar（10=null,
@favor_namevarchar（20='',
@favor_remarkvarchar（50='',
@proc_infovarchar（20output
as
begin
select@house_favorID=Max（house_favorIDfromtb_favor
if（@house_favorIDisnull
set@house_favorID='fav1001'--作动见编号就知道是什么表
else
set@house_favorID='fav'+cast（cast（substring（@house_favorID,4,4asint+1asvarchar（20
ifexists（selectfavor_namefromtb_favorwherefavor_name=@favor_name
set@proc_info='isHave'--控制不要输入两个同样的信息
else
begin
insertintotb_favorvalues（@house_favorID,@favor_name,@favor_remark
set@proc_info='ok'
end
end
GO
createprocproc_favor_update
@house_favorIDvarchar（10,
@favor_namevarchar（20='',
@favor_remarkvarchar（50='',
@proc_infovarchar（20output
as
begin
updatetb_favorsetfavor_name=@favor_name,favor_remark=@favor_remarkwherehouse_favorID=@house_favorID
if（@@error=0
set@proc_info='OK'
else
set@proc_info='systemerror:
'+cast（@@errorasvarchar（6
end
GO
createprocproc_fitment_delete
@house_fitmentIDvarchar（10=null
as
begin
deletefromtb_fitmentwherehouse_fitmentID=@house_fitmentID
end
GO
CREATEprocproc_fitment_insert
@house_fitmentIDvarchar（10=null,
@fitment_namevarchar（20='',
@fitment_remarkvarchar（50='',
@proc_infovarchar（20output
as
begin
select@house_fitmentID=Max（house_fitmentIDfromtb_fitment
print@house_fitmentID
if（@house_fitmentIDisnull
set@house_fitmentID='fit1001'--作动见编号就知道是什么表
else
set@house_fitmentID='fit'+cast（cast（substring（@house_fitmentID,4,4asint+1asvarchar（20
ifexists（selectfitment_namefromtb_fitmentwherefitment_name=@fitment_name
set@proc_info='isHave'--控制不要输入两个同样的信息
else
begin
insertintotb_fitmentvalues（@house_fitmentID,@fitment_name,@fitment_remark
set@proc_info='ok'
end
end
GO
createprocproc_fitment_update
@house_fitmentIDvarchar（10=null,
@fitment_namevarchar（20='',
@fitment_remarkvarchar（50=''
as
begin
updatetb_fitmentsetfitment_name=@fitment_name,fitment_remark=@fitment_remarkwherehouse_fitmentID=@house_fitmentID
end
GO
createprocproc_floor_delete
@house_floorIDvarchar（10=null
as
begin
deletefromtb_floorwherehouse_floorID=@house_floorID
end
GO
CREATEprocproc_floor_insert
@house_floorIDvarchar（10=null,
@floor_namevarchar（20='',
@floor_remarkvarchar（50='',
@proc_infovarchar（20output
as
begin
select@house_floorID=Max（house_floorIDfromtb_floor
if（@house_floorIDisnull
set@house_floorID='flo1001'--作动见编号就知道是什么表
else
set@house_floorID='flo'+cast（cast（substring（@house_floorID,4,4asint+1asvarchar（20
ifexists（selectfloor_namefromtb_floorwherefloor_name=@floor_name
set@proc_info='isHave'--控制不要输入两个同样的信息
else
begin
insertintotb_floorvalues（@house_floorID,@floor_name,@floor_remark
set@proc_info='ok'
end
end
GO
createprocproc_floor_update
@house_floorIDvarchar（10=null,
@floor_namevarchar（20='',
@floor_remarkvarchar（50=''
as
begin
updatetb_floorsetfloor_name=@floor_name,
floor_remark=@floor_remark
wherehouse_floorID=@house_floorID
end
GO
createprocproc_gov_delete
@gov_idvarchar（10=null
as
begin
deletefromtb_govwheregov_id=@gov_id
end
GO
CREATEprocproc_gov_insert
@gov_idvarchar（10=null,
@gov_namevarchar（20='',
@gov_remarkvarchar（50='',
@proc_infovarchar（20output
as
begin
select@gov_id=Max（gov_idfromtb_gov
if（@gov_idisnull
set@gov_id='gov1001'--作动见编号就知道是什么表
else
set@gov_id='gov'+cast（cast（substring（@gov_id,4,4asint+1asvarchar（20
ifexists（selectgov_namefromtb_govwheregov_name=@gov_name
set@proc_info='isHave'--控制不要输入两个同样的信息
else
begin
insertintotb_govvalues（@gov_id,@gov_name,@gov_remark
set@proc_info='ok'
end
end
GO
createprocproc_gov_update
@gov_idvarchar（10=null,
@gov_namevarchar（20='',
@gov_remarkvarchar（50=''
as
begin
updatetb_govsetgov_name=@gov_name,
gov_remark=@gov_remark
wheregov_id=@gov_id
end
GO
createprocproc_house_delete
@house_IDvarchar（10
as
begin
deletefromtb_housewherehouse_ID=@house_ID
end
GO
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
as
begin
set@house_ID=（selectMax（house_IDfromtb_house
declare@sqlvarchar（300
if（@house_IDisnull
set@house_ID='hou1001'
else
set@house_ID='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
end
GO
CREATEprocproc_house_update
@house_IDvarchar（10,
@house_companyNamevarchar（50,
@huose_typeIDvarchar（10,
@house_seatIDvarchar（10,
@house_fitmentIDvarchar（10,
@house_favorIDvarchar（10,
@house_mothedIDvarchar（10,
@huose_mapvarchar（50,
@house_pricefloat,
@house_floorIDvarchar（10,
@house_buildYearvarchar（10,
@house_areavarchar（20,
@house_remarkvarchar（50
as
begin
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
end
GO
CREATEprocproc_intent_insert
@intend_IDvarchar（10=null,
@user_idvarchar（10,
@huose_typeIDvarchar（10,
@house_seatIDvarchar（10,
@house_fitmentIDvarchar（10,
@house_floorIDvarchar（10,
@house_favorIDvarchar（10,
@house_mothedIDvarchar（10,
@house_pricenumeric（10,
@house_areavarchar（20
as
begin
declare@sqlvarchar（300
set@intend_ID=（selectMax（intent_IDfromtb_intent
if（@intend_IDisnull
set@intend_ID='int1001'
else
set@intend_ID='int'+cast（substring（@intend_ID,4,4+1asvarchar（10
insertintotb_intentvalues
（
@intend_ID,
@user_id,
@huose_typeID,
@house_seatID,
@house_fitmentID,
@house_floorID,
@house_favorID,
@house_mothedID,
@house_price,
@house_area
--上面先插入
set@sql='selecthouse_id房屋编号,user_ids户主编号,house_price价格,house_area房屋面积fromtb_housewhere
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
end
GO
createprocproc_login_delete
@employee_IDvarchar（10=null,
@login_namevarchar（20=null,
@ReturnInfonvarchar（50=nulloutput
as
begin
--删除时给你两种方法员工编号和用户名
if（@employee_IDisnulland@employee_IDisnull
set@ReturnInfo='xingxibuquan'
else
begin
deletefromtb_loginwhereemployee_ID=@employee_IDorlogin_name=@login_name
if（@@error=0
set@ReturnInfo='OK'
else
set@ReturnInfo='systeminfo'+cast（@@errorasvarchar（10
end
end
GO
createprocproc_login_insert
@login_idvarchar（10=null,
@employee_IDvarchar（10=null,
@login_namevarchar（20=null,
@login_pwdvarchar（15=null,
@login_powervarchar（10=null,
@ReturnInfonvarchar（50=nulloutput
a
升级会员 