The cursor does not include the table being modified or the table is not updatable through ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

The cursor does not include the table being modified or the table is not updatable through ...

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-12 : 10:54:45

Jeff writes "I have a stored procedure that creates a cursor and I'm trying to use the value of that cursor to update a record in another table. I get the error:

The cursor does not include the table being modified or the table is not updatable through the cursor.

Here's the SPROC can you do this?

CREATE PROCEDURE dbo.spMakeBatchCursor
@packCount int,
@carrier varchar(10),
@maxBatchID int = NULL
AS

SELECT DISTINCT a.CartContentID, c.CartID
INTO #PackCursor
FROM shipTransactions A, ShippingMethod B, Cart C
WHERE a.BatchID = 0
AND c.CartID = a.CartID
AND a.ShipMethodCode IN (select ShipMethodCode from ShippingMethod where carrier = @carrier)

EXEC spMakeBatchParameters @packCount, NULL, @carrier
SELECT @maxBatchID = (SELECT MAX(BatchID) FROM BatchInfo)

DECLARE @ThisCursor int -- holding variable
DECLARE @ThisCount int
SELECT @ThisCount = 1
DECLARE pCursor CURSOR -- create and fill cursor
GLOBAL -- callable outside this process
SCROLL -- Scroll back from @packCount
DYNAMIC -- STATIC
OPTIMISTIC
FOR
SELECT CartContentID FROM #PackCursor
FOR UPDATE OF cartContentID

-- Open Cursor and process to the Nth fetched record
OPEN pCursor
FETCH NEXT FROM pCursor INTO @ThisCursor
WHILE (@ThisCount<=@packCount) AND (@@FETCH_STATUS=0)
BEGIN
UPDATE ShipTransactions
SET batchID = @maxBatchID
WHERE CURRENT OF pCursor
SELECT @ThisCount = @ThisCount +1
FETCH NEXT FROM pCursor INTO @ThisCursor
END

CLOSE pCursor
DEALLOCATE pCursor
DROP TABLE #PackCursor"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-12 : 12:19:51

If the cursor is updateable for that table then you should be able to do it.
Whether it is a good idea to do it that way is another question though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-12 : 12:29:31

You're trying to UPDATE...WHERE CURRENT OF on a table that isn't included in the cursor, no wonder why it's not working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources