February 28, 2003 at 5:07 am
The following has been copied from Books Online. However there are no examples to support the 4th point. I was wondering where could I get a few examples
quote:
Cursor ProcessTransact-SQL cursors and API cursors have different syntax, but the following general process is used with all SQL Server cursors:
Associate a cursor with the result set of a Transact-SQL statement, and define characteristics of the cursor, such as whether the rows in the cursor can be updated.
Execute the Transact-SQL statement to populate the cursor.
Retrieve the rows in the cursor you want to see. The operation to retrieve one row or one block of rows from a cursor is called a fetch. Performing a series of fetches to retrieve rows in either a forward or backward direction is called scrolling.
Optionally, perform modification operations (update or delete) on the row at the current position in the cursor.
Close the cursor.
My question, is how do I perform modification operations on a cursor and get to see the data changes in the underlying data?
There are two specific things I am looking for.
1) Update the value.
2) Delete the current row of the cursor.
Also this might be fine if there is only one single underlying table. What happens if there are multiple tables?
Has anyone tried this out yet?
Cheers!
Abhijit
February 28, 2003 at 7:15 am
Check out the 'WHERE CURRENT OF' syntax in BOL. This will work in T-SQL with a cursor declared as FOR UPDATE.
USE Northwind
GO
BEGIN TRANSACTION
DECLARE crs_Update CURSOR FOR
SELECT OrderDate FROM Orders FOR UPDATE
OPEN crs_Update
FETCH NEXT FROM crs_Update
UPDATE Orders
SET OrderDate=GETDATE()
WHERE CURRENT OF crs_Update
SELECT *
FROM Orders
WHERE DATEDIFF(Day, OrderDate, GETDATE()) < 2
ROLLBACK TRANSACTION
Edited by - NPeeters on 02/28/2003 07:24:57 AM
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply