A SQL cursor is a database object that is used to retrieve data from a result set one row at a time. A SQL cursor is used when the data needs to be updated row by row.
In a SQL server database, a select statement returns a set of rows which is called a result set, But sometimes we want to work with a row at a time rather than the entire result set at once, In this situation you can use SQL Server cursor.
Why Use a Cursor in SQL Server
- It allows positioning at specific rows of the result set.
- It supports data modifications to the rows at the current position in the result set.
- Modifying data with a predefined set of data as the input to the cursor.
- Cursors can provide the first few rows before the whole result set is assembled.
Following are the diagram of CURSOR Life cycle.
Lets understand the cursor life cycle step by step.
First we declare a cursor as given below.
DECLARE <Cursor_name> CURSOR FOR <SQL_Query>
Cursor_name – Provide cursor name
SQL_Query – It will be a SELECT statement
DECLARE Cursor_name CURSOR FOR SELECT COL1, COL2, FROM tbl WHERE CONDITION..
Once you declare a cursor then, you need to retrieve the data from the SQL_query result which is done using the OPEN command as given below.
Just right after opening the cursor, the SQL_query is executed and the result set is stored in memory. The next step is to fetch row’s data and store the values in declared variables. For each column that we have in our SQL_Query there must be a declared variable to hold the value of those columns.
Variable datatype and column datatype must match also make sure that the order of the variables must be the same as the result set columns order.
FETCH NEXT FROM <cursor_name> [ INTO @Col1, @Col2 [ ,...n ] ]
Once operation is done, It is must to close the cursor. It releases the current result set and removes the locks from the rows used by the cursor.
After closing the cursor, you should also deallocating the cursor, that releases all the system resources associated with the cursor.
During the operations cursor uses function @@FETCH_STATUS
@@FETCH_STATUS is a system function that returns the status of the last FETCH statement issued against any opened cursor. It returns an integer value.
DECLARE @COL1 VARCHAR(50) DECLARE @COL1 VARCHAR(50) DECLARE Cursor_name CURSOR FOR SELECT COL1, COL2, FROM tbl WHERE CONDITION.. OPEN Cursor_name FETCH NEXT FROM Cursor_name INTO @COL1, @COL2 WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Cursor_name INTO @COL1, @COL2 END CLOSE Cursor_name DEALLOCATE Cursor_name
The purpose of this function is using it within while loop to keep fetching rows while the fetch statement is successful for that it returns value 0.
It returns -1 when there is no more row.
Following are the value that is returned by @@FETCH_STATUS
|0||The FETCH statement was successful|
|-1||The FETCH statement failed, or the row was beyond the result set|
|-2||The row fetched is missing|
|-9||The cursor is not performing a fetch operation|
You can also read more about @@FETCH_STATUS function – click to read
Lets implement the SQL Server Cursor.
First we create a table EMPLOYEE_INFO and insert some records into it.
CREATE TABLE EMPLOYEE_INFO ( EMP_ID INT, EMP_NAME VARCHAR(100), EMP_CODE VARCHAR(15) ) INSERT INTO EMPLOYEE_INFO ( EMP_ID, EMP_NAME, EMP_CODE, DEPT_ID) VALUES( (1, 'MANISH SINGH', 'EN-0034', 501), (2, 'MANOJ RAWAT', 'EN-0035', 501), (3, 'RAJESH KUMAWAT', 'EN-0036', 501), (4, 'SURAJ K', 'EN-0037', 502), (5, 'RUPESH SINGH', 'EN-0038', 502), (6, 'ARJUN PAL', 'EN-0039', 503), (7, 'GURPREET KAUR', 'EN-0040', 504), (8, 'TRILOK KUMAR', 'EN-0041', 504), (9, 'RANUK KHAN', 'EN-0041', NULL), (10, 'MANDEEP GREWAL', 'EN-0041', NULL)
The following cursor is used for retrieving employee information from table EMPLOYEE_INFO .
Cursor keeps fetching the rows from table until the FETCH_STATUS value is 0.
When all rows are fetched then @@FETCH_STATUS function returns -1.
DECLARE @emp_id INT, @emp_name VARCHAR(100), @emp_code VARCHAR(20); DECLARE emp_cr CURSOR FOR SELECT emp_id,emp_name,emp_code FROM Employee_info ORDER BY emp_id; OPEN emp_cr FETCH NEXT FROM emp_cr INTO @emp_id, @emp_name, @emp_code WHILE @@FETCH_STATUS = 0 BEGIN PRINT '' + CAST(@emp_id as varchar(10)) +' '+ @emp_name+ '' + @emp_code FETCH NEXT FROM emp_cr INTO @emp_id,@emp_name,@emp_code END CLOSE emp_cr; DEALLOCATE emp_cr;
Here is the output of CURSOR.
In above scenario, the cursor sets its position to the first row returned by the first SELECT and fetches the employee information such as EMP_ID, EMP_NAME, EMP_CODE values from EMPLOYEE_INFO table .
Then the second SELECT uses the variable value to get data from EMPLOYEE_INFO and the next row is fetched.
Cursor keeps fetching the rows from table until @@FFETCH_STATUS function returns -1.