The big gains (orders of magnitude) will come by changing cursors to set-based statements as you've done. If you can avoid row-by-row looping (through cursors or otherwise), then you should see some good gains there as well. In SQL Server 2005's CTEs and MARS we've removed some of the remaining need to use cursors and loops. But there are some situations where row-by-row processing still seems to be needed, and performing some non-set-based statement for a set of rows is the primary example .. executing a DBCC command for each database, for example.
If you find you are calling a stored proc for each row, perhaps you can pass a table containing the rows into the stored proc (perhaps by using a temp table) and then use set-base operations inside the stored proc, but there are times when you just need to call the sp row by row. If, after investigating all the set-based alternatives, you find you really do have to process rows one by one, then cursors are one way of iterating through a set of rows, and they do provide some good functionality with a well-defined behavior and you'll probably use your cursor together with a WHILE loop.
If you don't use a cursor to hold the rows to process, you'll have to retrieve a single row yourself each time through the loop; that'll probably be more coding for you, increase the potential for more bugs in your code, perhaps be more costly during execution, etc. So the trade-off becomes one of using cursors with a known downside, versus custom code with other potential drawbacks.
I'd say the "change cursors to while loops" statement oversimplifies the situation and falls way below the "change cursors to set-based operations where possible" primary guideline .. and it's unfortunate that it's at the top of the list in the article you mention.
Don
