25 September 2014

SQL Server - Cursor

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.

  • CREATE PROCEDURE Usp_cursor_test
    AS
      BEGIN
          –Declaring the  variables needed for cursor to store data
          DECLARE @Name VARCHAR(50)
          DECLARE @EmptypeID INT
          –Declaring the Cursor cur_print For name and Emptypeid in the Employeedetails table 
          DECLARE cur_print CURSOR FOR
            SELECT name,
                   emptypeid
            FROM   employee.employeedetails
          –After declaring we have to open the cursor 
          OPEN cur_print
          –retreives the First row from cursor and storing it into the variables. 
          FETCH NEXT FROM cur_print INTO @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. 
                FETCH NEXT FROM cur_print INTO @Name, @EmptypeID
            END
          –Closing the cursor 
          CLOSE cur_print
          – removes the cursor reference and relase cursor from memory 
          – very Important 
          DEALLOCATE cur_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.

 
back to top

Popular Posts