|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 09/12/2002 : 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" |
|
The cursor does not include the table being modified or the table is not updatable through ...
Forum Locked
