Did you find this useful?
Socialize it today.


SQL Cursors

Sunday, January 08, 2012

tags SQL

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

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

Head First SQL: Your Brain on SQL A Learners Guide SQL Cookbook