




版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、CREATE proc proc_clearasbegindelete from tb_user where user_ids not in(select user_ids from tb_house and user_type='lend'end GOcreate proc proc_employee_deleteemployee_ID varchar (10asbegindelete from tb_employee where employee_ID=employee_IDend GOCREATE proc proc_employee_insertemployee_ID
2、varchar (10=null,employee_name varchar (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 fro
3、m tb_employeeif(employee_ID is nullset employee_ID='emp1001'-作动见编号就知道是什么表elseset employee_ID='emp'+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
4、_address,gov_id,employee_study,employee_basepayEnd GOCREATE proc proc_employee_updateemployee_ID varchar (10=null,employee_name varchar (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
5、,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=e
6、mployee_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=0set proc_info='OK'elseset proc_info='system error
7、:'+cast(error as varchar(6end 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 nullset house_favorID='fav1
8、001'-作动见编号就知道是什么表elseset house_favorID='fav'+cast(cast(substring(house_favorID,4,4 as int+1 as varchar(20if exists(select favor_name from tb_favor where favor_name=favor_nameset proc_info='isHave'-控制不要输入两个同样的信息elsebegininsert into tb_favor values(house_favorID,favor_name,favor_re
9、markset proc_info='ok'endend 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(e
10、rror=0set proc_info='OK'elseset proc_info='system error:'+cast(error as varchar(6end 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
11、,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 nullset house_fitmentID='fit1001'-作动见编号就知道是什么表elseset house_fitmentID='fit'+
12、cast(cast(substring(house_fitmentID,4,4 as int+1 as varchar(20if exists(select fitment_name from tb_fitment where fitment_name=fitment_nameset proc_info='isHave'-控制不要输入两个同样的信息elsebegininsert into tb_fitment values(house_fitmentID,fitment_name,fitment_remarkset proc_info='ok'endend GO
13、create 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_flo
14、orID 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_floor
15、if(house_floorID is nullset house_floorID='flo1001'-作动见编号就知道是什么表elseset house_floorID='flo'+cast(cast(substring(house_floorID,4,4 as int+1 as varchar(20if exists(select floor_name from tb_floor where floor_name=floor_nameset proc_info='isHave'-控制不要输入两个同样的信息elsebegininsert int
16、o tb_floor values(house_floorID,floor_name,floor_remarkset proc_info='ok'endend 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_remarkwhere hous
17、e_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_i
18、d from tb_govif(gov_id is nullset gov_id='gov1001'-作动见编号就知道是什么表elseset gov_id='gov'+cast(cast(substring(gov_id,4,4 as int+1 as varchar(20if exists(select gov_name from tb_gov where gov_name=gov_nameset proc_info='isHave'-控制不要输入两个同样的信息elsebegininsert into tb_gov values(gov_id,
19、gov_name,gov_remarkset proc_info='ok'endend 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_remarkwhere gov_id=gov_idend GOcreate proc proc_house_deletehouse_ID varch
20、ar (10asbegindelete from tb_house where house_ID=house_IDend GOCREATE 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_mothedID varchar (
21、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(10asbeginset house_ID=(select Max(house_ID from tb_housedeclare sql varchar(300if(house_ID is nullset house_ID='hou1001'elseset house_ID
22、='hou'+cast(substring(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='
23、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_flo
24、orID+'''and house_favorID='''+house_favorID+'''and house_mothedID='''+house_mothedID+''''print sqlexec (sqlend GOCREATE proc proc_house_updatehouse_ID varchar (10,house_companyName varchar (50,huose_typeID varchar (10,house_seatID varch
25、ar (10,house_fitmentID varchar (10,house_favorID varchar (10,house_mothedID varchar (10,huose_map varchar (50,house_price float, house_floorID varchar (10,house_buildYear varchar (10, house_area varchar (20,house_remark varchar (50asbeginupdate tb_house set house_companyName=house_companyName,huose_
26、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_I
27、D=house_IDendGOCREATE proc proc_intent_insertintend_ID varchar (10=null,user_id varchar (10,huose_typeID varchar (10,house_seatID varchar (10,house_fitmentID varchar(10,house_floorID varchar (10,house_favorID varchar (10,house_mothedID varchar (10,house_price numeric (10,house_area varchar(20asbegin
28、declare sql varchar(300set intend_ID=(select Max(intent_ID from tb_intentif(intend_ID is nullset intend_ID='int1001'elseset intend_ID='int'+cast(substring(intend_ID,4,4+1 as varchar(10insert into tb_intent values(intend_ID,user_id,huose_typeID,house_seatID,house_fitmentID,house_floor
29、ID,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=
30、9;''+house_fitmentID+'''and house_floorID='''+house_floorID+'''and house_favorID='''+house_favorID+'''and house_mothedID='''+house_mothedID+''''print sqlexec (sqlend GOcreate proc proc_login_deleteemploye
31、e_ID varchar (10=null,login_name varchar (20=null,ReturnInfo nvarchar(50=null outputasbegin-删除时给你两种方法员工编号和用户名if(employee_ID is null and employee_ID is nullset ReturnInfo='xing xi bu quan'elsebegindelete from tb_login where employee_ID=employee_ID or login_name=login_nameif(error=0set ReturnI
32、nfo='OK'elseset ReturnInfo='system info'+cast(error as varchar(10endend GOcreate proc proc_login_insertlogin_id varchar(10=null,employee_ID varchar (10=null,login_name varchar (20=null,login_pwd varchar (15=null,login_power varchar (10=null,ReturnInfo nvarchar(50=null outputasbeginse
33、t login_id=(select Max(login_id from tb_loginif(login_id is nullset login_id='log1001'elseset 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_namebeginset employee_ID=(sele
34、ct employee_ID from tb_employee where employee_name=login_nameif 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_powerif(error=0set ReturnInfo='OK'elseset ReturnInfo=
35、'system info'+cast(error as varchar(10endelseset ReturnInfo='login table have this people'endelseset ReturnInfo='no this people'end GOcreate proc proc_login_selectlogin_name varchar (20=null,login_pwd varchar (15=null,ReturnInfo nvarchar(50=null outputasbeginif exists(select
36、login_name from tb_login where login_name=login_name and login_pwd=login_pwdselect ReturnInfo=login_power from tb_login where login_name=login_nameelseset ReturnInfo='none'end GOCREATE proc proc_login_updatelogin_name varchar (20=null,login_pwd varchar(15=null,login_power varchar(10=null,Ret
37、urnInfo nvarchar(50=null outputasbegin-删除时给你两种方法员工编号和用户名if not exists(select login_id from tb_login where login_name=login_nameset ReturnInfo='no this people'elsebeginif(login_power is nullupdate tb_login set login_pwd=login_pwd,login_power=login_power where login_name=login_nameelseupdate t
38、b_login set login_pwd=login_pwd,login_power=login_power where login_name=login_nameif(error=0set ReturnInfo='OK'elseset ReturnInfo='system info'+cast(error as varchar(10endend GO create proc proc_moneyandinfo_insertmoeny_ID varchar (10=null,Pay_Moeny numeric,emp_ID varchar (10,emp_na
39、me varchar (20,house_ID varchar(10,Pay_date varchar (50,moeny_remark varchar (100,lend_ID varchar (10,lend_Name varchar (20,lend_Phone varchar (30,want_ID varchar (10,want_Name varchar (20,want_Phone varchar (20asbeginset moeny_ID=(select Max(moeny_ID from tb_moneyandinfoif(moeny_ID is nullset moeny
40、_ID='mon1001'elseset moeny_ID='mon'+cast(substring(moeny_ID,4,4+1 as varchar(4insert 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_PhoneEnd GOcreate proc proc_mothed_deletehouse_mothed
41、ID 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
42、_mothedif(house_mothedID is nullset house_mothedID='mot1001'-作动见编号就知道是什么表elseset house_mothedID='mot'+cast(cast(substring(house_mothedID,4,4 as int+1 as varchar(20if exists(select mothed_name from tb_mothed where mothed_name=mothed_nameset proc_info='isHave'-控制不要输入两个同样的信息else
43、begininsert into tb_mothed values(house_mothedID,mothed_name,mothed_remarkset proc_info='ok'endend 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_re
44、mark=mothed_remarkwhere 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=''
45、;,proc_info varchar(20 outputasbeginselect house_seatID=Max(house_seatID from tb_seatif(house_seatID is nullset house_seatID='sea1001'-作动见编号就知道是什么表elseset house_seatID='sea'+cast(cast(substring(house_seatID,4,4 as int+1 as varchar(20if exists(select seat_name from tb_seat where seat_
46、name=seat_nameset proc_info='isHave'-控制不要输入两个同样的信息elsebegininsert into tb_seat values(house_seatID,seat_name,seat_remarkset proc_info='ok'endend GOcreate proc proc_seat_updatehouse_seatID varchar(10=null,seat_name varchar(20='',seat_remark varchar(50=''asbeginupdate t
47、b_seat set seat_name=seat_name,seat_remark=seat_remarkwhere 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_hous
48、e where house_id='''+huoseID+'''and user_type=''lend'' 'print sqlexec (sqlend 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_Ph
49、one='''+userPhone+'''and user_type=''want'''print sqlexec (sqlendGOcreate proc proc_studyDegree_deletestudyDegree_ID varchar(10=nullasbegindelete from tb_studyDegree where studyDegree_ID=studyDegree_IDend GOCREATE proc proc_studyDegree_insertstudyDegree_ID
50、 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 nullset studyDegree_ID='stu1001'-作动见编号就知道是什么表elseset studyDegree_ID='stu'
51、;+cast(cast(substring(studyDegree_ID,4,4 as int+1 as varchar(20if exists(select studyDegree_name from tb_studyDegree where studyDegree_name=studyDegree_nameset proc_info='isHave'-控制不要输入两个同样的信息elsebegininsert into tb_studyDegree values(studyDegree_ID,studyDegree_name,studyDegree_remarkset pro
52、c_info='ok'endend 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_remarkwhere studyDegree_ID=st
53、udyDegree_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=M
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 林地退股协议书
- 2025年建实务建筑试题及答案
- 查看调解协议书
- 2025年中级育婴试题库及答案
- 2025年权威php常见面试题及答案
- 2025年加油站三力测试题及答案
- 2025年c语言设计教程模拟试题及答案
- 栏目出资协议书
- 树木转让协议书
- 校企签约协议书
- 2025年内蒙古中考语文试卷
- 2025年十八项医疗核心制度必考试题库及答案
- 给银行提供雇佣合同范本
- 2025至2030中国度假旅游行业市场发展现状及发展趋势与投资风险报告
- 农村公路建设管理课件
- 共建共享健康中国课件
- 基层卫生院服务基层行-3.8.4药品不良反应管理
- 发改委专家评审管理办法
- 2025年广西中考语文试题卷(含答案及解析)
- 2025养殖场鸡舍承包合同范本
- 2025版标准正规劳动合同范本(房地产开发商专版)
评论
0/150
提交评论