FIX: Infinite Loop When Trying to UPDATE a cursor with a WHERE CURRENT OF Clause

SYMPTOMS

An UPDATE of a cursor with a WHERE CURRENT OF clause may cause an infinite loop if the DECLARE CURSOR statement includes a WHERE clause and the underlying table does not have a primary key. The following scripts demonstrate this problem:

SET NOCOUNT ON
GO

DROP TABLE t
GO

CREATE TABLE t
(
c1 CHAR(10) NULL,
c2 INT NOT NULL
)
GO

INSERT t VALUES (NULL, 1)
INSERT t VALUES (NULL, 2)

DECLARE @c1 CHAR(10)
DECLARE @c2 INT

DECLARE myCursor CURSOR FOR
SELECT c1, c2
FROM t
WHERE c2 = 1
FOR UPDATE

OPEN myCursor
FETCH NEXT FROM myCursor INTO @c1, @c2
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT
'@@FETCH_STATUS' = CONVERT (VARCHAR(10), @@FETCH_STATUS),
'C1' = CONVERT (VARCHAR(10), @c1),
'C2' = CONVERT (VARCHAR(10), @c2)
UPDATE t
SET c1 = 'updated'
WHERE CURRENT OF myCursor
END
FETCH NEXT FROM myCursor INTO @c1, @c2
END
CLOSE myCursor
DEALLOCATE myCursor
				

CREATE TABLE t
(
c1 CHAR(10) NULL,
c2 INT NOT NULL PRIMARY KEY
)
GO