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:
- Declare Transact-SQL variables to contain the data returned by the cursor.
- Associate a Transact-SQL cursor with a SELECT statement using the DECLARE CURSOR statement.
- Use the OPEN statement to execute the SELECT statement and populate the cursor.
- Use the FETCH INTO statement to fetch individual rows and have the data for each column moved into a specified variable.
- When you are finished with the cursor, use the CLOSE statement. You can reopen it if needed.
- 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
|
Did you find the page informational and useful? Share it using one of your favorite social sites.
Recommended Books & Training Resources
