sql增删改查存储过程精Word下载.docx

1、set employee_ID=emp1001-作动见编号就知道是什么表elseemp+cast（cast（substring（employee_ID,4,4 as int+1 as varchar（20insert into tb_employee values（employee_ID,employee_name,employee_sex,employee_birthday,employee_phone,employee_cardID,employee_address,gov_id,employee_study,employee_basepayEnd CREATE proc proc_emp

2、loyee_updateupdate tb_employee set employee_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_studywh

3、ere employee_ID=employee_IDGO create proc proc_favor_deletehouse_favorID varchar（10=null,proc_info varchar（20 outputdelete from tb_favor where house_favorID=house_favorID;if（error=0set proc_info=OKsystem error:+cast（error as varchar（6create proc proc_favor_insertfavor_name varchar（20=,favor_remark v

4、archar（50=select house_favorID=Max（house_favorID from tb_favorif（house_favorID is nullset house_favorID=fav1001fav+cast（cast（substring（house_favorID,4,4 as int+1 as varchar（20if exists（select favor_name from tb_favor where favor_name=favor_nameisHave-控制不要输入两个同样的信息insert into tb_favor values（house_fa

5、vorID,favor_name,favor_remarkokendcreate proc proc_favor_updatehouse_favorID varchar（10,update tb_favor set favor_name=favor_name,favor_remark=favor_remark where house_favorID=house_favorIDcreate proc proc_fitment_deletehouse_fitmentID varchar（10=nulldelete from tb_fitment where house_fitmentID=hous

6、e_fitmentIDCREATE proc proc_fitment_inserthouse_fitmentID varchar（10=null,fitment_name varchar（20=fitment_remark varchar（50=select house_fitmentID=Max（house_fitmentID from tb_fitmentprint house_fitmentIDif（house_fitmentID is nullset house_fitmentID=fit1001fit+cast（cast（substring（house_fitmentID,4,4

7、as int+1 as varchar（20if exists（select fitment_name from tb_fitment where fitment_name=fitment_nameinsert into tb_fitment values（house_fitmentID,fitment_name,fitment_remarkcreate proc proc_fitment_updateupdate tb_fitment set fitment_name=fitment_name,fitment_remark=fitment_remark where house_fitment

8、ID=house_fitmentIDcreate proc proc_floor_deletehouse_floorID varchar（10=nulldelete from tb_floor where house_floorID=house_floorIDCREATE proc proc_floor_inserthouse_floorID varchar（10=null,floor_name varchar（20=floor_remark varchar（50=select house_floorID=Max（house_floorID from tb_floorif（house_floo

9、rID is nullset house_floorID=flo1001flo+cast（cast（substring（house_floorID,4,4 as int+1 as varchar（20if exists（select floor_name from tb_floor where floor_name=floor_nameinsert into tb_floor values（house_floorID,floor_name,floor_remarkcreate proc proc_floor_updateupdate tb_floor set floor_name=floor_

10、name,floor_remark=floor_remarkwhere house_floorID=house_floorIDcreate proc proc_gov_deletegov_id varchar（10=nulldelete from tb_gov where gov_id=gov_idCREATE proc proc_gov_insertgov_id varchar（10=null,gov_name varchar（20=gov_remark varchar（50=select gov_id=Max（gov_id from tb_govif（gov_id is nullset g

11、ov_id=gov1001gov+cast（cast（substring（gov_id,4,4 as int+1 as varchar（20if exists（select gov_name from tb_gov where gov_name=gov_nameinsert into tb_gov values（gov_id,gov_name,gov_remarkcreate proc proc_gov_updateas update tb_gov set gov_name=gov_name,gov_remark=gov_remarkwhere gov_id=gov_idcreate proc

12、 proc_house_deletehouse_ID varchar （10delete from tb_house where house_ID=house_IDCREATE proc proc_house_inserthouse_ID varchar （10=null,house_companyName varchar （50,huose_typeID varchar （10,house_seatID varchar （10,house_state varchar （10,house_fitmentID varchar （10,house_favorID varchar （10,house

13、_mothedID varchar （10,huose_map varchar （50,house_price float, house_floorID varchar （10,house_buildYear int, house_area varchar （20,house_remark varchar （50,user_ids varchar（10set house_ID=（select Max（house_ID from tb_housedeclare sql varchar（300if（house_ID is nullset house_ID=hou1001hou+cast（subst

14、ring（house_ID,4,4+1 as varchar（10insert into tb_house values（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=select user_id 用户编号,house_pric

15、e 房价,house_area 房屋面积 from tb_intent wherehuose_typeID =+huose_typeID+and house_seatID=+house_seatID+and house_fitmentID=+house_fitmentID+and house_floorID=+house_floorID+and house_favorID=+house_favorID+and house_mothedID=+house_mothedID+print sqlexec （sqlCREATE proc proc_house_updatehouse_ID varcha

16、r （10,house_buildYear varchar （10, house_remark varchar （50update tb_house set 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

17、,house_floorID=house_floorID,house_buildYear=house_buildYear,house_area=house_area,house_remark=house_remarkwhere house_ID=house_IDCREATE proc proc_intent_insertintend_ID varchar （10=null,user_id varchar （10,house_fitmentID varchar（10,house_price numeric （10,house_area varchar（20set intend_ID=（selec

18、t Max（intent_ID from tb_intentif（intend_ID is nullset intend_ID=int1001int+cast（substring（intend_ID,4,4+1 as varchar（10insert into tb_intent values（intend_ID,user_id,house_areaselect house_id 房屋编号,user_ids 户主编号,house_price 价格,house_area 房屋面积 from tb_house wherecreate proc proc_login_deletelogin_name

19、 varchar （20=null,ReturnInfo nvarchar（50=null output-删除时给你两种方法员工编号和用户名if（employee_ID is null and employee_ID is nullset ReturnInfo=xing xi bu quandelete from tb_login where employee_ID=employee_ID or login_name=login_namesystem info+cast（error as varchar（10create proc proc_login_insertlogin_id varchar（10=null,login_pwd varchar （15=null,login_power varchar （10=null,a