22 August, 2010

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

2 comments:

Steve Gray said...

Mike:
I use this cursor template because it only requires the variables to be listed once, and you can use BREAK and CONTINUE to get out of the loop

http://devshed.us/Blogs/tabid/227/EntryId/30/Cursor-Template.aspx

mbsguru said...

Steve,

As always, you are awesome. That is much simpler than what I've been doing.

Thanks for sharing. I hope all is well.

MJ