Make no doubt about it... Cursors are an advanced subject and no new user of T-SQL should be allowed anywhere near a cursor or any other form of RBAR until they have had at least of year of intense set based training and experience. There are no redeeming qualities of cursor usage, especially in SQL Server 2005 and beyond, except that people who don't really know anything about a database can get in and try to do stuff at great expense to the server.

But when replacing cursors, we are letting go some important features that only cursors can provide to your code.

Like what? The ability for a row to fail insert because you didn't validate the information before you tried to insert it but don't want the other rows to fail? That's spaghetti code (throw it against the wall and see if it sticks) and it's both foolish and irresponsible... rollbacks are incredibly expensive compared to even using a cursor.

I believe that articles like this one serve as a great disservice to anyone new to databases and believe that it's horribly irresponsible of an author to try to bring any credibility to any form of RBAR programming in any RDBMS.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013

OK, I'll take up the case for using cursors for some types of processing. I've posted below a copy of a stored procedure I just finished. It uses a read-only cursor to loop through a set of records indicating when each lot starts being processed in each of a facility's processing lines. For each row as appropriate, the code ultimately calls two other stored procedures that write 'Attribution' and 'Association' events to another database of queued events. Anyway, long story short, there is no way you could do this processing in a set-based manner. Period.

Now, it could be argued that I should have pulled all this logic into a C# routine, and that may be the case, but I don't see much advantage, except that Visual Studio is a vastly better IDE. If I did do it in C#, then I'd still be issuing multiple calls to the event writing procedures, but now from within a C# loop. A loop is a loop and this keeps everything internal to SQL, without going back and forth between a windows service and SQL.

BTW, I also think that using cursors for one-off task is harmless. So what if it takes a second as opposed to a tenth of a second, when I save 5 minutes in development time by using a simple cursor?

--check to see if there are any child containers for test below select @intChildCount=count(*) from dbo.tblReadStreamCases where datCaseRead >= @datFrom -- in time range and datCaseRead < @datTo and intStationID in ( -- from a station of the current line SELECT S.intStationID FROM dbo.tblLines AS L INNER JOIN dbo.tblStations AS S ON L.intLineID = S.intLineID INNER JOIN dbo.tblFacilities F ON L.intFacilityID = F.intFacilityID WHERE (L.strLineName = @strLineCurrent) AND (F.strFacility = @strFacilityCurrent)

) and strCaseReading not in ( --not a child of any other container select strItemReading --think this is applied at end; --if not, could be peformance issue. <<< from tblReadStreamCases )

if @strFacility = @strFacilityCurrent and @strLine = @strLineCurrent and @intChildCount>0

--create a table to hold triples of parent and child containers --for the association event truncate table #tblTripleValue insert into #tblTripleValue select 'FacilityTag' as strType, cast(@guidNew as varchar(50))as strValue , cast(getdate() as varchar(50)) as strTimeStamp UNION select distinct (case when left(strCaseReading,2)='FT' then 'FacilityTag' else 'CodeRead' end) as strType , strCaseReading as strValue, cast(datCaseRead as varchar(50)) as strTimeStamp from dbo.tblReadStreamCases where datCaseRead >= @datFrom -- in time range and datCaseRead < @datTo and intStationID in ( -- from a station of the current line SELECT S.intStationID FROM dbo.tblLines AS L INNER JOIN dbo.tblStations AS S ON L.intLineID = S.intLineID INNER JOIN dbo.tblFacilities F ON L.intFacilityID = F.intFacilityID WHERE (L.strLineName = @strLineCurrent) AND (F.strFacility = @strFacilityCurrent)

) and strCaseReading not in ( --not a child of any other container select strItemReading --think this is applied at end; --if not, could be peformance issue. <<< from tblReadStreamCases )

There are times to use them. I'm jaded because I work on a product where a lot of C developers wrote the database originally. There is dynamic sql and cursors everywhere, even in triggers. They thought in loops instead of sets. I've seen cursors where you loop to call procs. In those cases, I just rewrote the procs to handle a set with a temp table. Make the temp table in the calling parent, child knows its there so when it returns to the parent you have your updated set. Sometimes the procs would just to query a table to get the status of things, so I just decentralized the code a bit. Many times you would do this 4000 times, where I only have to call the proc once. Performance was improved 100 to 1. I think you need to always try to solve set based first. Cursors are easy for programmers to wrap there heads around so in a I needed this done yesterday environment that wins out and when the product is released and the customer complains on performance you revisit it.