Overview

• DECLARE CURSOR defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates.
• In some scenarios, if there is a primary key on a table, a WHILE loop can be used instead of a cursor, without incurring in the overhead of a cursor.
  • However, there are scenarios where cursors are not only unavoidable, they are actually needed.
  • When that is the case, if there is no requirement to update tables based on the cursor, then use firehose cursors, meaning fast-forward and read-only cursors.

DECLARE @BusinessEntityId INT, @Name VARCHAR(50);

DECLARE Search_Cursor CURSOR FOR
SELECT TOP 1 [BusinessEntityID], [Name]
FROM Purchasing.Vendor;

OPEN Search_Cursor;

FETCH NEXT FROM Search_Cursor
INTO @BusinessEntityID, @Name;

-- Continue the loop as long as records have fetched.
WHILE @@FETCH_STATUS = 0
BEGIN
	-- Select previously fetched values.
	SELECT @BusinessEntityId, @Name;
	
	FETCH NEXT FROM Search_Cursor
	INTO @BusinessEntityID, @Name;
END

CLOSE Search_Cursor;
DEALLOCATE Search_Cursor;