Iterating through a dataset using CURSOR

Question

I am iterating through a dataset using a cursor, checking the value of specific columns (Myfield) of each record one by one. I am aware that this is contrary to how SQL can analyse a dataset, but I have to use this method in this case.

Is it possible to write a condition that would check the value of a defined column? Something like

If Myfield = 0 
    @number = @number + 1
ELSE 
    @number = @number + 2

I have defined the cursor like this:

SET @MyCursor = CURSOR FOR 
                    SELECT * FROM Table1

but apparently this is not sufficient for T-SQL - it throws the error

Invalid column name...

Answer 1

You need to declare the cursor for the desired result set and declare variables for each field in the row you wish to compare.

DECLARE @myField AS INT

DECLARE my_cursor CURSOR FOR
SELECT compareField FROM Table1

OPEN my_cursor

FETCH NEXT FROM my_cursor   
INTO @myField  

WHILE @@FETCH_STATUS = 0  
BEGIN
    If @myField = 0 THEN
         --do something--
    ELSE 
         --do something else--
    END IF

    FETCH NEXT FROM my_cursor
    INTO @myField
END

CLOSE my_cursor
DEALLOCATE my_cursor