Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    11

    Unanswered: 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

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    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?

  3. #3
    Join Date
    Oct 2004
    Posts
    11
    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

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    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.

    Code:
    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

  5. #5
    Join Date
    Oct 2004
    Posts
    11
    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?

  6. #6
    Join Date
    Feb 2004
    Posts
    492
    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)?

  7. #7
    Join Date
    Oct 2004
    Posts
    11
    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.

  8. #8
    Join Date
    Feb 2004
    Posts
    492
    so it's like...
    Code:
    ...
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •