Cursor is a variable in SQL Server Database which is used for row-by row operations. The cursor is so named because it indicates the current position in the resultset.
Let us look this example.
Here I am just taking 2 columns from a table and passing through the cursor and printing them.
CREATEPROCEDUREUsp_cursor_test AS BEGIN –Declaring the variables needed for cursor to store data DECLARE@NameVARCHAR(50) DECLARE@EmptypeIDINT –Declaring the Cursor cur_print For name and Emptypeid in the Employeedetails table DECLAREcur_printCURSORFOR SELECTname, emptypeid FROMemployee.employeedetails –After declaring we have to open the cursor OPENcur_print –retreives the First row from cursor and storing it into the variables. FETCHNEXTFROMcur_printINTO@Name,@EmptypeID – @@FETCH_STATUS returns the status of the last cursor FETCH statement issued against – any cursor currently opened by the connection. – @@FETCH_STATUS = 0 means The FETCH statement was successful. – @FETCH_STATUS = -1 The FETCH statement failed or the row was beyond the result set. – @@FETCH_STATUS = -2 The row fetched is missing. WHILE@@FETCH_STATUS=0 BEGIN –Operations need to be done,Here just printing the variables PRINT@Name PRINT@EmptypeID –retreives the NExt row from cursor and storing it into the variables. FETCHNEXTFROMcur_printINTO@Name,@EmptypeID END –Closing the cursor CLOSEcur_print – removes the cursor reference and relase cursor from memory – very Important DEALLOCATEcur_print END Note:
Once cursor is opened we have to close the cursor
After the usage cursor should be deallocated from the memory.
As a DBA , I will not recommend the usage of cursors in all scenarios because it affects performance, since for each result it will have a network round trip which will cause a major performance issue in large data sets. You can make use of case statement instead of cursors for some scenarios.