Tuesday, March 20, 2012

a big question for procedure

Dear all
i had one question would like to ask someone who can
give me a litter help
if my database data like it

traceno otherno username

0913377594 0913377594 roger
0913787170 0913787170 roger
0915534569 0915534569 roger
0925306029 0925306029 roger
0930443931 0930443931 roger
0936565187 0936565187 roger

drop procedure delete_phone_group
create procedure delete_phone_group
@.param1 varchar(30),
@.username varchar(5) with encryption
as
declare @.var varchar(30) , @.varotherno varchar(30)
select @.var = otherno
from phonegroup
where traceno = @.param1
and traceno != otherno
delete from phonegroup where traceno = otherno and traceno = (select otherno
from phonegroup
where traceno = @.param1
and traceno != otherno)
select @.varotherno = otherno
from phonegroup
where traceno = @.var
and traceno != otherno
print @.varotherno
insert into phonegroup(traceno,otherno,username)
values(@.param1,@.varotherno,@.username)
update phonegroup set phonecount = 1 where traceno = @.var and otherno = @.varotherno


when problem is when i edit one record in (phonegroup) this table
the script like it
insert into phonegroup(traceno,otherno,username)
valuse('0915534569' ,0913787170,'roger')
the data will be like this 0913787170 to be delete but when i try edit second data
insert into phonegroup(traceno,otherno,username)
valuse('0915534569' ,0936565187,'roger')


0913377594 0913377594 roger
0915534569 0913787170 roger
0915534569 0915534569 roger
0915534569 0936565187 roger
0925306029 0925306029 roger
0930443931 0930443931 roger
0936565187 0936565187 roger

but i donot why the second data it does not to be delete in my script

Have you done a DELETE statement. All I can see are inserts?|||yes i do i have donesql

No comments:

Post a Comment