SQL Cursors

Transact-SQL cursors are used mainly in stored procedures, triggers, and Transact-SQL scripts. Cursors make the contents of a result set available to other Transact-SQL statements.

The typical process for using a Transact-SQL cursor in a stored procedure or trigger is as follows:

1. Declare Transact-SQL variables to contain the data returned by the cursor.
2. Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement.
3. Use the OPEN statement to execute the SELECT statement and populate the cursor.
4. Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable.
5. When you are finished with the cursor, use the CLOSE statement. You can reopen it if needed.
6. The DEALLOCATE statement completely frees all resources allocated to the cursor, including the cursor name.

Syntax

DECLARE @variable1 dataType, @variable2 dataType 
DECLARE cursorName CURSOR FOR
SELECT statement...

OPEN cursorName

FETCH NEXT FROM cursorName
INTO @variable1, @variable2

WHILE @@FETCH_STATUS = 0
BEGIN

Do stuff with @variable1 and @variable2 such as PRINT,
create a message, INSERT INTO, UPDATE, etc...

FETCH NEXT FROM cursorName
INTO @variable1, @variable2
END
CLOSE cursorName
DEALLOCATE cursorName

Example

declare @product varchar(255)
declare @quantity integer 

DECLARE inventory CURSOR FOR
SELECT [Name], [Qty]
FROM myInventory

OPEN inventory

FETCH NEXT FROM inventory
INTO @product, @quantity

WHILE @@FETCH_STATUS = 0
BEGIN

print @product + ', ' + @quantity

FETCH NEXT FROM inventory
INTO @product, @quantity
END
CLOSE inventory
DEALLOCATE inventory