sql增删改查存储过程

《sql增删改查存储过程》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、CREATEprocproc_clearasbegindeletefromtb_userwhereuser_idsnotin(selectuser_idsfromtb_house)anduser_type='lend'endGOcreateprocproc_employee_delete@employee_IDvarchar(10)asbegindeletefromtb_employeewhereemployee_ID=@employee_IDendGOCREATEprocproc_employee_insert@employee_IDvarchar(1

2、0)=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_basepaynumericasbeginselect@employee_ID=Max(

3、employee_ID)fromtb_employeeif(@employee_IDisnull)set@employee_ID='emp1001'--作动见编号就知道是什么表elseset@employee_ID='emp'+cast(cast(substring(@employee_ID,4,4)asint)+1asvarchar(20))insertintotb_employeevalues(@employee_ID,@employee_name,@employee_sex,@employee_birthday,@employee_phone,@e

4、mployee_cardID,@employee_address,@gov_id,@employee_study,@employee_basepay)EndGOCREATEprocproc_employee_update@employee_IDvarchar(10)=null,@employee_namevarchar(20)=null,@employee_sexvarchar(10)=null,@employee_birthdaydatetime,@employee_phonevarchar(20),@employee_cardIDvarchar(20

5、),@employee_addressvarchar(50),@gov_idvarchar(16),@employee_studyvarchar(16),@employee_basepaynumericasbeginupdatetb_employeesetemployee_name=@employee_name,employee_sex=@employee_sex,employee_birthday=@employee_birthday,employee_phone=@employee_phone,employee_cardID=@employee_ca

6、rdID,employee_address=@employee_address,gov_id=@gov_id,employee_basepay=@employee_basepay,employee_study=@employee_studywhereemployee_ID=@employee_IDendGOcreateprocproc_favor_delete@house_favorIDvarchar(10)=null,@proc_infovarchar(20)outputasbegindeletefromtb_favorwherehouse_favor

7、ID=@house_favorID;if(@@error=0)set@proc_info='OK'elseset@proc_info='systemerror:'+cast(@@errorasvarchar(6))endGOcreateprocproc_favor_insert@house_favorIDvarchar(10)=null,@favor_namevarchar(20)='',@favor_remarkvarchar(50)='',@proc_infovarchar(20)outputasbeginselect@house_favorID=M

8、ax(house_favorID)fromtb_favorif(@house_f