sql增删改查存储过程.doc

CREATE proc proc_clearasbegindelete from tb_user where user_ids not in(select user_ids from tb_house ) and user_type=lendend GOcreate proc proc_employee_deleteemployee_IDvarchar(10)asbegindelete from tb_employee where employee_ID=employee_IDend GOCREATE proc proc_employee_insertemployee_IDvarchar(10)=null,employee_namevarchar(20)=null,employee_sex varchar(10)=null,employee_birthday datetime,employee_phone varchar(20),employee_cardID varchar(20),employee_address varchar(50),gov_id varchar (16),employee_study varchar(16),employee_basepay numeric asbeginselect employee_ID=Max(employee_ID) from tb_employeeif(employee_ID is null)set employee_ID=emp1001-作动见编号就知道是什么表elseset employee_ID=emp+cast(cast(substring(employee_ID,4,4) as int)+1 as varchar(20)insert into tb_employee values(employee_ID,employee_name,employee_sex,employee_birthday,employee_phone,employee_cardID,employee_address,gov_id,employee_study,employee_basepay)End GOCREATE proc proc_employee_updateemployee_IDvarchar(10)=null,employee_namevarchar(20)=null,employee_sex varchar(10)=null,employee_birthday datetime,employee_phone varchar(20),employee_cardID varchar(20),employee_address varchar(50),gov_id varchar (16),employee_study varchar(16),employee_basepay numeric asbeginupdate 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_studywhere employee_ID=employee_IDend GO create proc proc_favor_deletehouse_favorID varchar(10)=null,proc_info varchar(20) outputasbegindelete from tb_favor where house_favorID=house_favorID;if(error=0)set proc_info=OKelseset proc_info=system error:+cast(error as varchar(6)end GOcreate proc proc_favor_inserthouse_favorID varchar(10)=null,favor_name varchar(20)=,favor_remark varchar(50)=,proc_info varchar(20) outputasbeginselect house_favorID=Max(house_favorID) from tb_favorif(house_favorID is null)set house_favorID=fav1001-作动见编号就知道是什么表elseset house_favorID=fav+cast(cast(substring(house_favorID,4,4) as int)+1 as varchar(20)if exists(select favor_name from tb_favor where favor_name=favor_name)set proc_info=isHave-控制不要输入两个同样的信息elsebegininsert into tb_favor values(house_favorID,favor_name,favor_remark)set proc_info=okendend GOcreate proc proc_favor_updatehouse_favorID varchar(10),favor_name varchar(20)=,favor_remark varchar(50)=,proc_info varchar(20) outputasbeginupdate tb_favor set favor_name=favor_name,favor_remark=favor_remark where house_favorID=house_favorIDif(error=0)set proc_info=OKelseset proc_info=system error:+cast(error as varchar(6)end GOcreate proc proc_fitment_deletehouse_fitmentID varchar(10)=nullasbegindelete from tb_fitment where house_fitmentID=house_fitmentIDend GOCREATE proc proc_fitment_inserthouse_fitmentID varchar(10)=null,fitment_name varchar(20)=,fitment_remark varchar(50)=,proc_info varchar(20) outputasbeginselect house_fitmentID=Max(house_fitmentID) from tb_fitmentprint house_fitmentIDif(house_fitmentID is null)set house_fitmentID=fit1001-作动见编号就知道是什么表elseset house_fitmentID=fit+cast(cast(substring(house_fitmentID,4,4) as int)+1 as varchar(20)if exists(select fitment_name from tb_fitment where fitment_name=fitment_name)set proc_info=isHave-控制不要输入两个同样的信息elsebegininsert into tb_fitment values(house_fitmentID,fitment_name,fitment_remark)set proc_info=okendend GOcreate proc proc_fitment_updatehouse_fitmentID varchar(10)=null,fitment_name varchar(20)=,fitment_remark varchar(50)=asbeginupdate tb_fitment set fitment_name=fitment_name,fitment_remark=fitment_remark where house_fitmentID=house_fitmentIDend GOcreate proc proc_floor_deletehouse_floorID varchar(10)=nullasbegindelete from tb_floor where house_floorID=house_floorIDend GOCREATE proc proc_floor_inserthouse_floorID varchar(10)=null,floor_name varchar(20)=,floor_remark varchar(50)=,proc_info varchar(20) outputasbeginselect house_floorID=Max(house_floorID) from tb_floorif(house_floorID is null)set house_floorID=flo1001-作动见编号就知道是什么表elseset house_floorID=flo+cast(cast(substring(house_floorID,4,4) as int)+1 as varchar(20)if exists(select floor_name from tb_floor where floor_name=floor_name)set proc_info=isHave-控制不要输入两个同样的信息elsebegininsert into tb_floor values(house_floorID,floor_name,floor_remark)set proc_info=okendend GOcreate proc proc_floor_updatehouse_floorID varchar(10)=null,floor_name varchar(20)=,floor_remark varchar(50)=asbeginupdate tb_floor set floor_name=floor_name,floor_remark=floor_remark where house_floorID=house_floorIDend GOcreate proc proc_gov_deletegov_id varchar(10)=nullasbegindelete from tb_gov where gov_id=gov_idend GOCREATE proc proc_gov_insertgov_id varchar(10)=null,gov_name varchar(20)=,gov_remark varchar(50)=,proc_info varchar(20) outputasbeginselect gov_id=Max(gov_id) from tb_govif(gov_id is null)set gov_id=gov1001-作动见编号就知道是什么表elseset gov_id=gov+cast(cast(substring(gov_id,4,4) as int)+1 as varchar(20)if exists(select gov_name from tb_gov where gov_name=gov_name)set proc_info=isHave-控制不要输入两个同样的信息elsebegininsert into tb_gov values(gov_id,gov_name,gov_remark)set proc_info=okendend GOcreate proc proc_gov_updategov_id varchar(10)=null,gov_name varchar(20)=,gov_remark varchar(50)=as beginupdate tb_gov set gov_name=gov_name,gov_remark=gov_remark where gov_id=gov_idend GOcreate proc proc_house_deletehouse_IDvarchar(10)asbegindelete from tb_house where house_ID=house_IDend GOCREATE proc proc_house_inserthouse_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_ids varchar(10)asbeginset house_ID=(select Max(house_ID) from tb_house)declare sql varchar(300)if(house_ID is null)set house_ID=hou1001elseset house_ID=hou+cast(substring(house_ID,4,4)+1 as varchar(10)insert 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_price 房价,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 (sql)end GOCREATE proc proc_house_updatehouse_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)asbeginupdate 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,house_floorID=house_floorID,house_buildYear=house_buildYear,house_area=house_area,house_remark=house_remarkwhere house_ID=house_IDendGOCREATE proc proc_intent_insertintend_IDvarchar(10)=null,user_idvarchar(10),huose_typeIDvarchar(10),house_seatIDvarchar(10),house_fitmentID varchar(10),house_floorIDvarchar(10),house_favorIDvarchar(10),house_mothedIDvarchar(10),house_price numeric(10),house_area varchar(20)asbegindeclare sql varchar(300)set intend_ID=(select Max(intent_ID) from tb_intent)if(intend_ID is null)set intend_ID=int1001elseset intend_ID=int+cast(substring(intend_ID,4,4)+1 as varchar(10) insert into tb_intent values(intend_ID,user_id,huose_typeID,house_seatID,house_fitmentID,house_floorID,house_favorID,house_mothedID,house_price,house_area)-上面先插入set sql=select house_id 房屋编号,user_ids 户主编号,house_price 价格,house_area 房屋面积 from tb_house 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 (sql)end GOcreate proc proc_login_deleteemployee_IDvarchar(10)=null,login_namevarchar(20)=null,ReturnInfo nvarchar(50)=null outputasbegin-删除时给你两种方法员工编号和用户名if(employee_ID is null and employee_ID is null)set ReturnInfo=xing xi bu quanelsebegindelete from tb_login where employee_ID=employee_ID or login_name=login_nameif(error=0)set ReturnInfo=OKelseset ReturnInfo=system info+cast(error as varchar(10)endend GOcreate proc proc_login_insertlogin_idvarchar(10)=null,employee_IDvarchar(10)=null,login_namevarchar(20)=null,login_pwdvarchar(15)=null,login_powervarchar(10)=null,ReturnInfo nvarchar(50)=null outputasbeginset login_id=(select Max(login_id) from tb_login)if(login_id is null)set login_id=log1001elseset login_id=log+cast(substring(login_id,4,4)+1 as varchar(10)-1在添加之前要看本单位是否有这个人有这个人则记下他的编号if exists (select employee_ID from tb_employee where employee_name=login_name)beginset employee_ID=(select employee_ID from tb_employee where employee_name=login_name)if not exists (select login_id from tb_login where employee_id=employee_ID)-2这个人是否以注册过了如注册过则不能在注册begininsert into tb_login values(login_id,employee_ID,login_name,login_pwd,login_power)if(error=0)set ReturnInfo=OKelseset ReturnInfo=system info+cast(error as varchar(10)endelseset ReturnInfo=login table have this peopleendelseset ReturnInfo=no this peopleend GOcreate proc proc_login_selectlogin_namevarchar(20)=null,login_pwdvarchar(15)=null,ReturnInfo nvarchar(50)=null outputasbegin if exists(select login_name from tb_login where login_name=login_name and login_pwd=login_pwd)select ReturnInfo=login_power from tb_login where login_name=login_name elseset ReturnInfo=noneend GOCREATE proc proc_login_updatelogin_namevarchar(20)=null,login_pwdvarchar(15)=null,login_power varchar(10)=null,ReturnInfo nvarchar(50)=null outputasbegin-删除时给你两种方法员工编号和用户名if not exists(select login_id from tb_login where login_name=login_name)set ReturnInfo=no this peopleelsebeginif(login_power is null)update tb_login set login_pwd=login_pwd,login_power=login_power where login_name=login_nameelseupdate tb_login set login_pwd=login_pwd,login_power=login_power where login_name=login_nameif(error=0)set ReturnInfo=OKelseset ReturnInfo=system info+cast(error as varchar(10)endend GO create proc proc_moneyandinfo_insertmoeny_IDvarchar(10)=null,Pay_Moenynumeric,emp_IDvarchar(10),emp_name varchar(20),house_IDvarchar(10),Pay_date varchar(50),moeny_remarkvarchar(100),lend_IDvarchar(10),lend_Namevarchar(20),lend_Phone varchar(30),want_ID varchar(10),want_Namevarchar(20),want_Phone varchar(20)asbeginset moeny_ID=(select Max(moeny_ID) from tb_moneyandinfo)if(moeny_ID is null)set moeny_ID=mon1001elseset moeny_ID=mon+cast(substring(moeny_ID,4,4)+1 as varchar(4)insert into tb_moneyandinfo values(moeny_ID,Pay_Moeny,emp_ID,emp_name,house_ID,Pay_date,moeny_remark,lend_ID,lend_Name,lend_Phone,want_ID,want_Name,want_Phone)End GOcreate proc proc_mothed_deletehouse_mothedID varchar(10)=nullasbegindelete from tb_mothed where house_mothedID=house_mothedIDendGOCREATE proc proc_mothed_inserthouse_mothedID varchar(10)=null,mothed_name varchar(20)=,mothed_remark varchar(50)=,proc_info varchar(20) outputasbeginselect house_mothedID=Max(house_mothedID) from tb_mothedif(house_mothedID is null)set house_mothedID=mot1001-作动见编号就知道是什么表elseset house_mothedID=mot+cast(cast(substring(house_mothedID,4,4) as int)+1 as varchar(20)if exists(select mothed_name from tb_mothed where mothed_name=mothed_name)set proc_info=isHave-控制不要输入两个同样的信息elsebegininsert into tb_mothed values(house_mothedID,mothed_name,mothed_remark)set proc_info=okendend GOcreate proc proc_mothed_updatehouse_mothedID varchar(10)=null,mothed_name varchar(20)=,mothed_remark varchar(50)=asbeginupdate tb_mothed set mothed_name=mothed_name,mothed_remark=mothed_remark where house_mothedID=house_mothedIDend GOcreate proc proc_seat_deletehouse_seatID varchar(10)=nullasbegindelete from tb_seat where house_seatID=house_seatIDend GOCREATE proc proc_seat_inserthouse_seatID varchar(10)=null,seat_name varchar(20)=,seat_remark varchar(50)=,proc_info varchar(20) outputasbeginselect house_seatID=Max(house_seatID) from tb_seatif(house_seatID is null)set house_seatID=sea1001-作动见编号就知道是什么表elseset house_seatID=sea+cast(cast(substring(house_seatID,4,4) as int)+1 as varchar(20)if exists(select seat_name from tb_seat where seat_name=seat_name)set proc_info=isHave-控制不要输入两个同样的信息elsebegininsert into tb_seat values(house_seatID,seat_name,seat_remark)set proc_info=okendend GOcreate proc proc_seat_updatehouse_seatID varchar(10)=null,seat_name varchar(20)=,seat_remark varchar(50)=asbeginupdate tb_seat set seat_name=seat_name,seat_remark=seat_remark where house_seatID=house_seatIDend GOcreate proc proc_select_IwantHousehuoseID varchar(20)=nullasbegindeclare sql varchar(200)-房子和房主的对应关系set sql=select user_Ids,user_names,user_phone from tb_user where user_ids in (select user_ids from tb_house where house_id=+huoseID+and user_type=lend) print sqlexec (sql)end GOCREATE proc proc_select_IwantUseruserPhone varchar(20)=nullasbegindeclare sql varchar(200)-房子和房主的对应关系set sql=select user_Ids,user_names,user_phone from tb_user where user_Phone=+userPhone+and user_type=wantprint sqlexec (sql)endGOcreate proc proc_studyDegree_deletestudyDegree_ID varchar(10)=nullasbegindelete from tb_studyDegree where studyDegree_ID=studyDegree_IDend GOCREATE proc proc_studyDegree_insertstudyDegree_ID varchar(10)=null,studyDegree_name varchar(20)=,studyDegree_remark varchar(50)=,proc_info varchar(20) outputasbeginselect studyDegree_ID=Max(studyDegree_ID) from tb_studyDegreeif(studyDegree_ID is null)set studyDegree_ID=stu1001-作动见编号就知道是什么表elseset studyDegree_ID=stu+cast(cast(substring(studyDegree_ID,4,4) as int)+1 as varchar(20)if exists(select studyDegree_name from tb_studyDegree where studyDegree_name=studyDegree_name)set proc_info=isHave-控制不要输入两个同样的信息elsebegininsert into tb_studyDegree values(studyDegree_ID,studyDegree_name,studyDegree_remark)set proc_info=okendend GOcreate proc proc_studyDegree_updatestudyDegree_ID varchar(10)=null,studyDegree_name varchar(20)=,studyDegree_remark varchar(50)=asbeginupdate tb_studyDegree set studyDegree_name=studyDegree_name,studyDegree_remark=studyDegree_remark where studyDegree_ID=studyDegree_IDend GOCREATE proc proc_type_deletehuose_typeID varchar(10)=nullasbegindelete from tb_type where huose_typeID=huose_typeIDend GOCREATE proc proc_type_inserthouse_typeID varchar(10)=null,type_names varchar(20)=,type_remark varchar(50)=,proc_info varchar(20) outputasbeginselect house_typeID=Max(huose_typeID) from tb_typeif(house_typeID is null)set ho