November 19, 2009 at 9:00 am
If you wrap a cursor in a "Begin Tran" statement and then have a "IF Error=!0 Rollback" statement after the Deallocate, will the Rollback catch every update / insert that was inside the cursor and roll it back?
My instincts say yes, but part of me also says no. So please enlighten me.
November 19, 2009 at 9:53 am
It will, a cursor doesn't change anything.
Example:
CREATE TABLE #a(id int IDENTITY(1,1), a char(1))
INSERT INTO #a(a)
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a' UNION ALL
SELECT 'a'
GO
DECLARE @id int
BEGIN TRAN
DECLARE Test CURSOR FOR
SELECT id FROM #a
OPEN Test
FETCH NEXT FROM Test
INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #a
SET a = 'b'
WHERE id = @id
FETCH NEXT FROM Test
INTO @id
END
CLOSE Test
DEALLOCATE Test
SELECT * FROM #a
ROLLBACK TRAN
SELECT * FROM #a
November 20, 2009 at 4:47 am
Thanks, Garadin, for both the response and the test code. It's appreciated.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply