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.