sql增删改查存储过程.doc

CREATE proc proc_clear as begin delete from tb_user where user_ids not in(select user_ids from tb_house and user_type=lend end GO create proc proc_employee_delete @employee_ID varchar (10 as begin delete from tb_employee where employee_ID=@employee_ID end GO CREATE proc proc_employee_insert @employee_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, @employee_study varchar(16, @employee_basepay numeric as begin select @employee_ID=Max(employee_ID from tb_employee if(@employee_ID is null set @employee_ID=emp1001--作动见编号就知道是什么表 else set @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 GO CREATE proc proc_employee_update @employee_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, @employee_study varchar(16, @employee_basepay numeric as begin update 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_study where employee_ID=@employee_ID end GO create proc proc_favor_delete @house_favorID varchar(10=null, @proc_info varchar(20 output as begin delete from tb_favor where house_favorID=@house_favorID; if(@@error=0 set @proc_info=OK else set @proc_info=system error:+cast(@@error as varchar(6 end GO create proc proc_favor_insert @house_favorID varchar(10=null, @favor_name varchar(20