Post
Ask Question

Cursor Explained in SQL Server

Introduction

 
In this blog, I will explain the SQL Cursor. A cursor is a database object used to retrieve data from a result set, one row at a time. It can also be used when the data needs to be updated row by row. In order to work with a cursor, we need to perform some steps, as follows:
  1. Declaring the cursor for initializing the memory
  2. Opening the cursor for allocating the memory
  3. Fetching the cursor for retrieving the data
  4. Closing the cursor to release the allocated memory

Return Statement

  • 0 -- FETCH statement was successful.
  • 1 -- FETCH statement failed or the row was beyond the result set.
  • 2 -- Row fetched is missing.
Syntax
  1. DECLARE EX_CURSOR_NAME CURSOR  
  2. FOR  
  3. select Statement  
  4. OPEN EX_CURSOR_NAME  
  5. FETCH NEXT FROM EX_CURSOR_NAME INTO Variables  
  6. WHILE @@FETCH_STATUS = 0  
  7. BEGIN  
  8. FETCH NEXT FROM EX_CURSOR_NAME INTO Variables  
  9. END  
  10. CLOSE EX_CURSOR_NAME  
  11. DEALLOCATE EX_CURSOR_NAME  
Example
  1. print 'First_name'  
  2. DECLARE @ID INT    
  3. DECLARE @FIRST_NAME NVARCHAR(MAX)    
  4. DECLARE CURSOR_NAME CURSOR    
  5. FOR    
  6. SELECT ID,FIRST_NAME  FROM TB_NAME  
  7. OPEN  CURSOR_NAME  
  8. FETCH NEXT FROM  CURSOR_NAME INTO  @ID ,@FIRST_NAME  
  9. WHILE @@FETCH_STATUS = 0    
  10. BEGIN    
  11.     PRINT  'ID: ' + CONVERT(NVARCHAR(MAX),@ID)+  '  FIRST_NAME '+@FIRST_NAME  
  12.    FETCH NEXT FROM CURSOR_NAME  INTO  @ID ,@FIRST_NAME  
  13. END    
  14. CLOSE CURSOR_NAME  
  15. DEALLOCATE CURSOR_NAME  
Limitations
  1. Cursors can be faster than a while loop, but they have more overhead.
  2. Cursor fetches a row each time.
  3. It is a network round trip, so performance and speed is slow.
  4. The cursors have slower performance because the cursor updates the tables row by row.
  5. There are restrictions on the SELECT statements that can be used.