Simple Examples for CURSOR

CURSOR:

Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates.

SELECT * FROM TBLEMPLOYEE

Eid EName Email
10 aa aa@a.com
20 bb b@b.com
30 aaa c@c.com
10 aa da@c.com
20 dddd b@b.com

Examples:

DECLARE @Employee VARCHAR(MAX)

DECLARE TBLCURSOR CURSOR FOR
SELECT ENAME +' - '+CAST(EID AS VARCHAR) FROM TBLEMPLOYEE

OPEN TBLCURSOR
FETCH NEXT FROM TBLCURSOR INTO @Employee

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @Employee
FETCH NEXT FROM TBLCURSOR INTO @Employee
END
CLOSE TBLCURSOR
DEALLOCATE TBLCURSOR

Output:
aa - 10
bb - 20
aaa - 30
aa - 10
dddd - 20

Examples 2:

DECLARE Employee_cursor CURSOR FOR
SELECT e.Eid
FROM dbo.TblEmployee AS e
WHERE Salary <>
(SELECT MAX(Salary)
FROM dbo.tblemployee AS s
WHERE e.Eid = s.Eid) ;
OPEN Employee_cursor;
FETCH FROM Employee_cursor;
UPDATE dbo.tblEmployee
SET EName = 'ssss'
WHERE CURRENT OF Employee_cursor;
CLOSE Employee_cursor;
DEALLOCATE Employee_cursor;

GO