How to use a SQL cursor to iterate data.

Dave

It has been a while since I have used a cursor to manipulate data. The DBA’s I have spoken to have differing opinions on whether or even when they should be used. I am not a SQL specialist but I do tend to use them for one off tasks or for scheduled jobs that normally run our of hours.

I have included a template cursor here for reference for the next time.

-- Declare the list of fields here that you need access to withing the cursor
-- The fields below are an example.
DECLARE @AccountNo VARCHAR(10)
DECLARE @AccountName VARCHAR(255)
DECLARE @FirstName VARCHAR(50)
DECLARE @LastName VARCHAR(50)
DECLARE @Email VARCHAR(255)

DECLARE @MainCursor CURSOR
SET @MainCursor = CURSOR FAST_FORWARD
FOR
	-- Here we query the database retrieving the fields needed
	SELECT AccountNo, AccountName, FirstName, LastName, Email FROM Accounts
OPEN @MainCursor
	-- And execute the initial fetch for the first row of data
	FETCH NEXT FROM @MainCursor
	INTO @AccountNo, @AccountName, @FirstName, @LastName, @Email
WHILE @@FETCH_STATUS = 0
BEGIN

	/*
		Data manipulation statements go here.
	*/

	-- fetch next row if it exists 
	FETCH NEXT FROM @MainCursor
	INTO @AccountNo, @AccountName, @FirstName, @LastName, @Email
END
CLOSE @MainCursor
DEALLOCATE @MainCursor

Leave a Reply