SQL: Simple SQL Server Cursor

5/19/2015 2:55:09 PM

The following is a simple example of a SQL Server Cursor

--declare variables to hold data fetched by cursor
DECLARE		@userId int,
			@firstName varchar(200),
			@lastname varchar(200),
			@counter int

set @counter = 0


--create the cursor
DECLARE		MyCursor CURSOR FOR
SELECT		Id, FirstName, LastName
FROM		Users
ORDER BY	Id asc


--open cursor and grab first row of data
OPEN	MyCursor
FETCH	MyCursor
INTO	@userId, @firstName, @lastName


-- start the main processing loop.
WHILE @@Fetch_Status = 0   
BEGIN
	set @counter = @counter + 1

	--Print
	PRINT ('Counter Says: ' + CAST(@counter as varchar(10)))

	--do something
	--update	users
	--set		users.FirstName = @firstName,
	--		users.LastName = @lastname
	--where	users.id = @userId
		
	-- Get the next row of data
	FETCH	MyCursor
	INTO	@userId, @firstName, @lastName
END


--never forget to close and deallocate your cursor
CLOSE MyCursor
DEALLOCATE MyCursor