SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 The cursor does not include the table being modified or the table is not updatable through ...
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/12/2002 :  10:54:45  Show Profile  Visit AskSQLTeam's Homepage
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

United Kingdom
12543 Posts

Posted - 09/12/2002 :  12:19:51  Show Profile  Visit nr's Homepage
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 09/12/2002 :  12:29:31  Show Profile  Visit robvolk's Homepage
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000