Hi,
To generate serial number you can use below Example:
Create proc proc_UpdateGenSerial
As
Begin
declare cur_GenSerial cursor
For select itemcode,icode from tbl_TableName
Declare @itemcode int,@icode int,@oldicode int,@newserialnum int
set @newserialnum = 1
open cur_GenSerial
fetch next from cur_GenSerial into @itemcode,@icode
while(@@FETCH_STATUS=0)
Begin
if @itemcode = 1
Begin
set @oldicode = @icode
End
if(@oldicode = @icode)
Begin
Update tbl_GenSerialNum
Set serialnum = @newserialnum
Where itemcode = @itemcode
set @newserialnum = @newserialnum + 1
End
Else
Begin
set @newserialnum = 1
Update tbl_GenSerialNum
Set serialnum = @newserialnum
Where itemcode = @itemcode
set @newserialnum = @newserialnum + 1
set @oldicode = @icode
End
fetch next from cur_GenSerial into @itemcode,@icode
End
Close cur_GenSerial
Deallocate cur_GenSerial
End
I think above Example should be helpful to solve your problem...