Row by row analysis and execution with Cursors

Being able to do row by row data analysis and manipulation with SQL could be something that's missing from your toolbox.  Whether you're converting data or developing a customization, cursors can prove to be quite useful.  I use this template often.  Loops are generally preferred to cursors for better performance but I still go to cursors often.

This example will pass all of the ACTINDX and ACTNUMST values from the GL00105 table into a cursor called curCursorName and select the variable values, displaying them in the SMS query results pane, before going to the next.

All you have to do is 1) declare a variable for each value you need to select into your cursor, 2) replace the select statement with the data set on which you need to do row by row analysis or manipulation, 3) write you own logic for the EXECUTE THE LOGIC section, and 4) replace the variables in the FETCH sections with your own.  Remember, the values must be selected into the cursor in the same order the variables are fetched. 

--DECLARE THE VARIABLES

DECLARE @ACTINDX INT,

@ACTNUMST VARCHAR(50)

--DECLARE THE CURSOR

DECLARE curCursorName cursor fast_forward for

-- SELECT THE VARIABLES INTO THE CURSOR

SELECT ACTINDX, ACTNUMST

FROM GL00105 T with (nolock)

--OPEN THE CURSOR

OPEN curCursorName

--FETCH A RECORD FROM THE CURSOR

FETCH next from curCursorName

into @ACTINDX, @ACTNUMST

WHILE (@@FETCH_STATUS = 0)

BEGIN

--EXECUTE THE LOGIC

select @ACTINDX, @ACTNUMST

--GET THE NEXT RECORD

FETCH next from curCursorName

into @ACTINDX, @ACTNUMST

END

--CLOSE AND DEALLOCATE THE CURSOR

CLOSE curCursorName

DEALLOCATE curCursorName