rows deletion affected by cursor

Hello,

I am using a cursor to navigate on data...of a table...
inside the while @.@.fetch_status = 0 command
I want to delete some rows from the table(temporary table)
in order to not be processed...
The problem is that I want this deletion to affect the rows the cursor has.

I declared a dynamic cursor but it does not work.

Does anyone know how I can do this??

Thanks :)Perhaps the fetch into @.var is already executed, changes to the resultset do not affect values in variables? If not, could you post your code?|||The deleted is executed before the fetch next statement.

My code is:

Set @.items = Cursor
For
Select T.patentrynr,t.patcode,t.patname,cast(t.groupid As Nvarchar(10)),g.groupname,
(select Sum(idaxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid )as Idaxia,
(select Sum(tamaxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid) As Tamaxia,
(select Sum(insuraxia) From @.trans T1
Where T.groupid = T1.groupid Group By T1.groupid) As Insuraxia,
Itemnum,indvrate,indvamount,tamrate,
Tamamount,insurrate,insuramount,maxqty,tamname,gro upnum,t.groupid
From @.trans T Inner Join @.rates R
On R.groupid = T.groupid And R.groupid Is Not Null
And T.itemid!=cast(t.groupid As Nvarchar(10))
Inner Join Groups G On G.groupid = T.groupid
Order By T.patentrynr,t.groupid

Open @.items

Fetch From @.items Into
@.patentrynr,@.patcode,@.patname,@.itemid,@.itemname,@.i daxia,@.tamaxia,@.insuraxia,@.itemnum,
@.indvrate,@.indvamount,@.tamrate,
@.tamamount,@.insurrate,@.insuramount,@.maxqty,@.tamnam e,@.groupnum,@.groupid

While @.@.fetch_status = 0
Begin
If @.indvamount Is Not Null And @.groupnum Is Not Null And
@.idaxia Is Not Null And @.idaxia!=0
Begin
If @.groupnum > @.maxqty And @.maxqty Is Not Null
Begin
Set @.indvposo = @.maxqty*@.indvamount
End
Else
Begin
Set @.indvposo = @.groupnum*@.indvamount
End
End
Else If @.indvrate Is Not Null And @.idaxia Is Not Null
Begin
Set @.indvposo = @.idaxia*(@.indvrate/100)
End


Insert Into @.result (patentrynr,patcode,patname,tamname,itemid,itemnam e,indvtziros,
Tamtziros,insurtziros,indvpososto,tampososto,insur pososto,parakrat)
Values(@.patentrynr,@.patcode,@.patname,@.tamname,@.ite mid,@.itemname,@.idaxia,@.tamaxia,@.insuraxia,
@.indvposo,@.tamposo,@.insurposo,@.parakrat)


Set @.idaxia=null
Set @.tamaxia =null
Set @.insuraxia=null
Set @.itemnum=null
Set @.indvrate=null
Set @.indvamount=null
Set @.tamrate=null
Set @.tamamount=null
Set @.insurrate=null
Set @.insuramount=null
Set @.maxqty=null
Set @.indvposo=null
Set @.tamposo=null
Set @.insurposo=null

Delete From @.trans Where Patentrynr = @.patentrynr
And Groupid = @.groupid


Fetch Next From @.items
Into @.patentrynr,@.patcode,@.patname,@.itemid,@.itemname,
@.idaxia,@.tamaxia,@.insuraxia,@.itemnum,
@.indvrate,@.indvamount,@.tamrate,@.tamamount,
@.insurrate,@.insuramount,@.maxqty,@.tamname,@.groupnum ,@.groupid
End --end While|||I don't see a reason why rows would not be deleted from the variable table. Are you saying no rows at all are deleted from the table? What are the values of @.groupid and @.patentrynr prior the delete (what rows match)?

I've setup an example that basically does the same, perhaps it gives you an idea.

use monkey
go

set nocount on

declare @.varTab table ( myInt integer, myValue varchar(3))

insert into @.varTab (myInt, myValue) values (1, 'aaa')
insert into @.varTab (myInt, myValue) values (2, 'aaa')
insert into @.varTab (myInt, myValue) values (3, 'aaa')
insert into @.varTab (myInt, myValue) values (4, 'aaa')

declare @.myint integer
declare @.myvalue varchar(3)
declare cur_tab1 cursor DYNAMIC
for select myInt, myvalue from @.varTab

declare @.mtef cursor
set @.mtef = cur_tab1 -- ?

open @.mtef
fetch next from @.mtef into @.myint, @.myvalue
while @.@.fetch_status = 0
begin
-- update tab1 set myValue = 'bbb' where myInt = @.myInt + 1
delete from @.varTab where myInt = @.myInt
select 'myInt: ', @.myint, @.myvalue

fetch next from @.mtef into @.myint, @.myvalue
end

select * from @.varTab

deallocate @.mtef
go|||I have executed your example an it works...fine
but when i added a select statement in my code
before the fetch next statement to find what the table hoes
I found that the values are deleted...
I have also added a select statement after the fetch next...
to find out the values that will be next processed
and they are the next values found in the table before the deletion...

Do I have to change anything in the cursor declaration?|||I'm not sure what you're saying. So the rows are deleted from @.trans? What do you mean with "Do I have to change anything in the cursor declaration?" (assuming the delete works)?|||The delete works but...
I want the fetch next to fetch the next row in the table after the deletion.
This does not work.
It cursor fetches the next row in the table as it was before the deletion.
It seems that the data in the cursor is static...and it is not affected by the deletion.|||so it's like...

...
for select myInt, myvalue
from @.varTab
order by myInt -- order by clause
...
delete from @.varTab where myInt = @.myInt + 1 -- First run: delete myInt = 2
...
deallocate @.mtef
deallocate cur_tab1

The output includes all four rows...

Does your cursor declaration include an 'ORDER BY'-clause?
If it does, your cursor is converted into a KEYSET-cursor (see BOL on this).
What I know from keyset cursors is from BOL, so I'm a bit guessing here but I think the deletes are not visible because it's not the cursor doing the deletes.