Scott Chamberlain Scott Chamberlain - 1 year ago 62
SQL Question

Cursor says its read only even though I declared it "for update"

I am trying to update a row inside a cursor. What I am trying to do is update a chain of records with OLD_QTY and NEW_QTY. However when I try to do my update it gives the error

The cursor is READ ONLY
even though I included
for update of OLD_QTY, NEW_QTY
in my declration. It makes no difference if I include
in the select statement.

declare @current_inv_guid uniqueidentifier
declare @last_inv_guid uniqueidentifier
declare @current_vid int
declare @last_vid int
--declare @current_new_qty money
declare @last_new_qty money
--declare @current_old_qty money

declare iaCursor cursor
for select INV_GUID, old_VID
from #IA
order by INV_GUID, old_vid, ENTRY_NUM
for update --of OLD_QTY, NEW_QTY
open iaCursor
Fetch next from iaCursor into @current_inv_guid, @current_vid --, @current_old_qty, @current_new_qty

while @@fetch_status = 0
--test to see if we hit a new chain.
if(@last_inv_guid <> @current_inv_guid or @current_vid <> @last_vid)
set @last_new_QTY = (select #lots.QTY_RECEIVED from #lots where #lots.INV_GUID = @current_inv_guid and LOT_VID = @current_vid)
set @last_inv_guid = @current_inv_guid
set @last_vid = @current_vid

--update the current link in the chain
update #ia
set OLD_QTY = @last_new_QTY,
NEW_QTY = @last_new_QTY + QTY_CHANGE,
@last_new_QTY = @last_new_QTY + QTY_CHANGE
where current of iaCursor

--get the next link
fetch next from iaCursor into @current_inv_guid, @current_vid --, @current_old_qty, @current_new_qty

close iaCursor
deallocate iaCursor

Answer Source

Besides the reason you mentioned in your answer, what you're attmepting to do runs counter to the way SQL is meant to be used. Try to update the data in sets, not by rows.

I'm not positive, as I don't know your table design, but I believe the following should work. You may get better performance out of this. In particular, I'm assuming that QTY_CHANGE is coming from #ia, although this may not be the case.

UPDATE #ia as a set (OLD_QTY, NEW_QTY) = (SELECT #lots.QTY_RECEIVED + (COUNT(b.*) * a.QTY_CHANGE), 
                                                 #lots.QTY_RECEIVED + ((COUNT(b.*) + 1) * a.QTY_CHANGE)
                                          FROM #lots
                                          LEFT JOIN #ia as b
                                          ON b.INV_GUID = a.INV_GUID
                                          AND b.OLD_VID = a.OLD_VID
                                          AND b.ENTRY_NUM < a.ENTRY_NUM
                                          WHERE #lots.INV_GUID = a.INV_GUID
                                          AND #lots.LOT_VID = a.OLD_VID)
              FROM #lots
              WHERE #lots.INV_GUID = a.INV_GUID
              AND #lots.LOT_VID = a.OLD_VID)


... the previous version of the answer was written with a DB2 perspective, although it would otherwise be db-agnostic. It also had the problem of using the same value of QTY_CHANGE for every row, which is unlikely. This should be a more idiomatic SQL Server 2008 version, as well as being more likely to output the correct answer:

WITH RT AS (SELECT #IA.inv_guid, #IA.old_vid, #IA.entry_num,
                   COALESCE(MAX(#Lots.qty_received), 0) + 
                         SUM(#IA.qty_change) OVER(PARTITION BY #IA.inv_guid, #IA.old_vid 
                                                  ORDER BY #IA.entry_num) 
                        AS running_total                                      
                       FROM #IA
                       LEFT JOIN #Lots
                              ON #Lots.inv_guid = #IA.inv_guid
                                 AND #Lots.lot_vid = #IA.old_vid)
SET #IA.old_qty = RT.running_total - #IA.qty_change, #IA.new_qty = RT.running_total
  ON RT.inv_guid = #IA.inv_guid
     AND RT.old_vid = #IA.old_vid
     AND RT.entry_num = #IA.entry_num
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download