Cursors: Create and use a simple cursor

Date Revised: August 14 2009

Shows how to create a simple cursor to look thru records and count the number of duplicate values in a field. I know there is a better way for doing this but this is meant to be a simple example. A more effecient way to get these same results can be found here.

/*

How to create a simple cursor to look thru records

and count the number of duplicate values in a field

*/

-- Database to use

USE DatabaseToUse;

-- Declare local variables

DECLARE @Temp    VARCHAR(50);

DECLARE @NumOcc INT;

-- Declare a cursor and populate it with a result set

-- using a select statement

DECLARE myCursor CURSOR FOR

    SELECT fieldName,

        COUNT(fieldName) AS NumOccurrences

        FROM dbo.TableName

        GROUP BY fieldName

        HAVING ( COUNT(fieldName) > 1 );

OPEN myCursor;

-- Returns the result row immediately following the current row and increments

-- the current row to the row returned. If FETCH NEXT is the first fetch

-- against a cursor, it returns the first row in the result set.

-- NEXT is the default cursor fetch option.

FETCH NEXT FROM myCursor INTO @Temp, @NumOcc;

-- @@FETCH_STATUS will return either -2, -1, or 0

-- -2 row is missing

-- -1 fetch failed

--  0 successfull

WHILE @@FETCH_STATUS = 0

    BEGIN

        PRINT 'FieldName: ' + @Temp + ' = ' + CAST(@NumOcc AS VARCHAR(2)) + ' occurences.';

        FETCH NEXT FROM myCursor INTO @Temp, @NumOcc;

END

-- Clean up resoruces used

CLOSE myCursor;

DEALLOCATE myCursor;

GO