My recommendation is to lookup "cursors [SQL Server]" in Books Online (the help system that comes with SQL Server) because CURSORs are a big subject. In the meantime, here's a simple cursor that reads a couple of values from a table and displays them. If you're in the GRID mode, it will end with an error. I've commented the code so you can see what each piece does.

USE AdventureWorks;

GO

--===== Declare some cursor related variables

DECLARE @EmployeeID INT,

@Title NVARCHAR(50)

--===== Declare the cursor using a SELECT

DECLARE Employee_Cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY

FOR

SELECT EmployeeID, Title

FROM HumanResources.Employee;

--===== Open the cursor to begin using it.

-- This is where a static cursor gets loaded

-- into a temp table

OPEN Employee_Cursor;

--===== Start an infinite loop. We'll break out later...

WHILE 1 = 1

BEGIN

--===== Read a row from the cursor

FETCH NEXT FROM Employee_Cursor

INTO @EmployeeID, @Title;

--===== If the read above returns NO row, exit the loop

-- because we're done

IF @@FETCH_STATUS <> 0 BREAK;

--===== If we're still here, then there was a row to be read.

-- We can now process that row.

SELECT @EmployeeID, @Title;

--===== This marks the end of the While Loop which automatically continues

-- up to here until we hit the "BREAK" in the code above.

END;

--======== Release any locks held open by the cursor and then drop the

-- cursor structure.

CLOSE Employee_Cursor;

DEALLOCATE Employee_Cursor;

GO

So far as what to use a CURSOR for goes, the answer is almost always that you shouldn't use a CURSOR. I'll also tell you that writing a Temp Table or Table Variable to step through using a While Loop is nothing more than a poor man's CURSOR and should also be avoided 99.99% of the time.

The only time I'll condone (never mind allow in my shop) is when you're trying to do something to all tables in a database and other object control related things. Even then, you can get away with concatenated commands rather than using a CURSOR.

Don't be fooled by recursive CTEs or the use of things like sp_MSForEachTable... recursive CTEs are generally a form of hidden RBAR and sp_MSForEachTable is nothing more than a huge, very ugly CURSOR in the background.

The answer is, it allows people who don't know how to do high performance, set-based code to still get to their data and do some processing. CURSORs were originally meant to make it a bit easier to do something to, say, all tables in a database but others have used them to overcome their lack of set-based knowledge. Generally speaking, cursors should be avoided no matter what the cost because they are usually terrible for performance and resource usage. I've never put a CURSOR into production code and about the only time I use a While Loop is to step through file names during T-SQL imports of files. Even that isn't RBAR processing... it's a control loop to load thousands/millions of rows for each file in a set-based manner.

Just to summarize and emphasize... if you use a CURSOR, While Loop, or any form of recursion to affect just one row at a time (RBAR), there's a very, very, high probability that you're doing it the wrong way whether you can think of a set-based method or not.