sql增刪改查存儲過程

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