Dhanushka's Web Blog

In programming languages such as c# and java we use data structures like arrays and lists to store a set of data in the memory so we can iterate through them and do some processing.
when it comes to sql the alternative solution is the T-SQL CURSOR

The basic steps of using a cursor are as follows
1)Declare a cursor and associate it with a SELECT statement, the basic characteristics of the cursor needs to be defined here as well
2)Open and populate the cursor
3)Fetch a row from the cursor
4)Process the fetched row inside the loop after checking the fetch status
5)close and de-allocate the cursor

lets write a simple cursor and demonstrate the above mentioned steps

--Declare the variables needed, to do the necessary processing
DECLARE @NAME VARCHAR(MAX)

---(1)---
DECLARE myCursor CURSOR FOR
SELECT employee_name FROM EMPLOYEES

* alternatively you can declare a local variable of type CURSOR and set its structure as well
DECLARE @myCursor CURSOR
SET @myCursor= CURSOR FOR SELECT employee_name FROM EMPLOYEES
* you can also set the cursor for read only mode to gain more performance or to update mode to update the rows as you iterate through them
DECLARE myCursor CURSOR READ_ONLY
FOR SELECT employee_name FROM EMPLOYEES
OR
DECLARE myCursor CURSOR
FOR SELECT employee_name FROM EMPLOYEES
FOR UPDATE OF designation

---(2)---
OPEN myCursor
--(3)---
FETCH NEXT FROM myCursor INTO @NAME
---(4)---
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @NAME
FETCH NEXT FROM myCursor INTO @NAME
END
* here FETCH_STATUS is used to determine the boundary status of the iterator, following are values it returns depending on the situation
0 - fetch statement was executed successfully
-1 - row that needs to be fetched is beyond the result set
-2 - row that needs to be fetched is missing
* FETCH_STATUS is defined globally for all the cursors on a connection, so when we are using it we need to be careful to check the status immediately after the fetch before another fetch occur.
* you can get the number of rows in the cursors result set using the @@CURSOR_ROWS variable

---(5)---
CLOSE myCursor
DEALLOCATE myCursor
* after using the cursor we must close and de-allocate it so there wont be any references for the cursor in the memory