Cursor Explained in SQL Server

In this blog, I will explain the SQL Cursor.

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

DECLARE EX_CURSOR_NAME CURSOR
FOR
select Statement
OPEN EX_CURSOR_NAME
FETCH NEXT FROM EX_CURSOR_NAME INTO Variables
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM EX_CURSOR_NAME INTO Variables
END
CLOSE EX_CURSOR_NAME
DEALLOCATE EX_CURSOR_NAME

Example

print 'First_name'
DECLARE @ID INT
DECLARE @FIRST_NAME NVARCHAR(MAX)
DECLARE CURSOR_NAME CURSOR
FOR
SELECT ID,FIRST_NAME  FROM TB_NAME
OPEN  CURSOR_NAME
FETCH NEXT FROM  CURSOR_NAME INTO  @ID ,@FIRST_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT  'ID: ' + CONVERT(NVARCHAR(MAX),@ID)+  '  FIRST_NAME '+@FIRST_NAME
   FETCH NEXT FROM CURSOR_NAME  INTO  @ID ,@FIRST_NAME
END
CLOSE CURSOR_NAME
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.