Cursors - TSQL Tutorial
In this chapter you can learn how to work with cursors using operations like declare
cursor, create procedure, fetch, delete, update, close, set, deallocate.
Cursor operations
Declare cursor
Create procedure
Open cursor
Close cursor
Fetch cursor
Deallocate cursor
Delete
Update
Declare cursors
Declare cursor Syntax:
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_query_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ] ;
Declare simple cursor example:
DECLARE product_cursor CURSOR
FOR SELECT * FROM model.dbo.products;
OPEN product_cursor
FETCH NEXT FROM product_cursor;
Create procedure
Create procedure example:
USE model;
GO
IF OBJECT_ID ( 'dbo.productProc', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.productProc;
GO
CREATE PROCEDURE dbo.productProc
@varCursor CURSOR VARYING OUTPUT
AS
SET NOCOUNT ON;
SET @varCursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT product_id, product_name
FROM dbo.products;
OPEN @varCursor;
GO
Open cursors
Open cursor Syntax:
OPEN { { cursor_name } | cursor_variable_name }
Open cursor example:
USE model;
GO
DECLARE Student_Cursor CURSOR FOR
SELECT id, first_name, last_name, country
FROM dbo.students WHERE country != 'US';
OPEN Student_Cursor;
FETCH NEXT FROM Student_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Student_Cursor;
END;
CLOSE Student_Cursor;
DEALLOCATE Student_Cursor;
GO
Close cursors
Close cursor Syntax:
CLOSE { { cursor_name } | cursor_variable_name }
Close cursor example:
USE model;
GO
DECLARE Student_Cursor CURSOR FOR
SELECT ID, FIRST_NAME FROM dbo.students;
OPEN Student_Cursor;
FETCH NEXT FROM Student_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Student_Cursor;
END;
CLOSE Student_Cursor;
DEALLOCATE Student_Cursor;
GO
Fetch cursors
Fetch cursor Syntax:
FETCH
[ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
{ { cursor_name } | @cursor_variable_name }
[ INTO @variable_name [ ,...n ] ] ;
Fetch in cursors example:
USE model;
GO
DECLARE Student_Cursor CURSOR FOR
SELECT id, first_name, last_name, country
FROM dbo.students WHERE country = 'US';
OPEN Student_Cursor;
FETCH NEXT FROM Student_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Student_Cursor;
END;
CLOSE Student_Cursor;
DEALLOCATE Student_Cursor;
GO
Deallocate cursors
When the cursor is deallocated, the data structures comprising the cursor are released by
Microsoft SQL Server.
Deallocate cursor Syntax:
DEALLOCATE { { cursor_name } | cursor_variable_name }
Deallocate cursor example:
USE model;
GO
DECLARE @CursorName CURSOR
SET @CursorName = CURSOR LOCAL SCROLL FOR
SELECT * FROM dbo.students;
DEALLOCATE @CursorName;
SET @CursorName = CURSOR LOCAL SCROLL FOR
SELECT * FROM dbo.students;
GO
Delete in cursors
Delete in cursors example:
USE model;
GO
DECLARE MyCursor CURSOR FOR
SELECT *
FROM dbo.students
WHERE first_name = 'David' AND last_name = 'BROWN' AND id = 6 ;
OPEN MyCursor;
FETCH FROM MyCursor;
DELETE FROM dbo.students WHERE CURRENT OF MyCursor;
CLOSE MyCursor;
DEALLOCATE MyCursor;
GO
Update in cursors
Update in cursors example:
USE model;
GO
DECLARE test_cursor CURSOR LOCAL FOR
SELECT id, first_name, last_name, section
FROM dbo.students WHERE id = 2;
OPEN test_cursor;
FETCH test_cursor;
UPDATE dbo.students
SET section = 'Medicine'
FROM dbo.students
WHERE CURRENT OF test_cursor;
GO
SELECT id, first_name, last_name, section FROM dbo.students;
GO
Resources:
www.tsql.info/
www.tsql.info/cursors/cursors.php
