--------------------成员表存储过程--------------------------------
create proc sp_chengyuan
 @aciton int,--1:增加2:修改3:删除
 @cyid int ,
 @cyname varchar(20) ,--成员名
 @cyphone varchar(11) ,--成员手机号
 @cyyinhang varchar(30),--银行名
 @cycardID int,--银行账号
 @cyoffer money, --成员贡献值
 @cyreturn money --成员回报值
with encryption
as
set nocount on
set rowcount 0
set transaction isolation level read uncommitted
 
   declare @error_no integer,   --错误号
           @error_info varchar(255)---错误信息
 
    select   @aciton =isnull(@action,2),
             @cyid =isnull(@cyid,1)
             @cyname =isnull(@cyname,'') ,
             @cyphone =isnull(@cyphone,'') ,
             @cyyinhang =isnull(@cyyinhang,'') ,
             @cycardID =isnull(@cycardID,'') ,
             @cyoffer =isnull(@cyoffer,'0.00') ,
             @cyreturn =isnull(@cyreturn,'0.00')
 
    select     @error_no =0,   
               @error_info =''
begin transaction
if @action=1 --增加成员
begin
   insert into chengyuan(cyname,cyphone,cyyinhang,cycardID,cyoffer,cyreturn)
   values(@cyname,@cyphone,@cyyinhang,@cycardID,@cyoffer,@cyreturn)
   if @@error !=0  or  @@rowcount !=1
   begin
   select @error_no=1001
          @error_info='[1001]插入成员信息出错'
   rollback transaction
   return @error_no
   end 
end
else if @action=2 --修改成员
begin
   update chengyuan
   set  cyphone=@cyphone,cyyinhang=@cyyinhang,
        cycardID=@cycardID,cyoffer=@cyoffer,cyreturn=@cyreturn
   where cyid=@cyid and cyname=@cyname
   
   if @@error !=0  or  @@rowcount !=1
   begin
   select @error_no=1001
          @error_info='[1002]查询成员信息出错'
   rollback transaction
   return @error_no
   end 
end
else if @action=3 --删除成员
begin
   delete from chengyuan where cyid=@cyid and cyname=@cyname
   
   if @@error !=0  or  @@rowcount !=1
   begin
   select @error_no=1001
          @error_info='[1002]查询成员信息出错'
   rollback transaction
   return @error_no
   end 
end
commit transaction

if @error_no!=0
  return @error_no
return 0