Cursor (STATIC)
February 15th, 2010
No comments
STATIC – data is scrollable, it require space on tempdb and changes to data are not visible right away. It does not allow modification.
CREATE TABLE #temp (k1 INT IDENTITY, c1 int)
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
INSERT INTO #temp DEFAULT VALUES
– SELECT * FROM #temp
DECLARE c CURSOR STATIC FOR SELECT k1, c1 FROM #temp
OPEN c
FETCH c
UPDATE #temp SET c1=2 WHERE k1 = 2
FETCH c
– FETCH PRIOR FROM c
SELECT * FROM #temp
CLOSE c
DEALLOCATE c
DROP TABLE #temp
Output:
k1 c1
———– ———–
1 NULL
k1 c1
———– ———–
2 NULL
k1 c1
———– ———–
1 NULL
2 2
3 NULL
4 NULL