In this article I am trying to explain the concept of Cursor in SQL Server.
Definition:
Cursor is an object of database which is used to retrieve data from result set row by row. In other words a cursor is a set of rows together with a pointer that identifies a current row. We use cursor when we need to update records in a table row by row.
Cursor Life Cycle:
1-Declare Cursor
A cursor is declared by DECLARE CURSOR SQL command. We can declare a cursor by following syntax:
DECLARE cur_name CURSOR
[LOCAL | GLOBAL] --define the scope of cursor
[FORWARD_ONLY | SCROLL] --define cursor movements
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD] -- type of cursor
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
FOR select_statement -- SQL Select statement
FOR UPDATE [column1,column2,...columnn] -- columns that to be updated
2-Open Cursor
A cursor can be open in two way either locally or globally. Syntax for open cursor:
OPEN [GLOBAL] cur_name --by default it is local
3-Fetch Cursor
After opening a you can fetch cursor. Syntax for fetch cursor:
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cur_name
INTO @var1,@var2…..@varn
4-Close Cursor
Close statement close cursor explicitly. Syntax for close cursor:
CLOSE cur_name
5-Deallocate Cursor
To free the resources allocated by cursor we deallocate the cursor.Syntax for deallocate cursor
DEALLCATE cur_name
Example of Cursor
DECLARE @FirstName varchar(50)
DECLARE @LastName varchar(50)
DECLARE cur_name CURSOR
STATIC FOR SELECT FirstName,LastName FROM user1
OPEN cur_name
if @@CURSOR_ROWS>0
BEGIN
FETCH NEXT FROM cur_name INTO @FirstName,@LastName
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'First Name:'+@FirstName+',Last Name:'+@LastName
FETCH NEXT FROM cur_name INTO @FirstName,@LastName
END
END
CLOSE cur_name
DEALLOCATE cur_name
Leave Comment