In this article, I’m trying to explain the concept of cursor in SQL server.

Whenever you execute a select statement, it returns you a set of rows. The entire set is one thing and not a selection of individual rows. This output is useful for the batch-processing application but less appealing for applications where the user might want to work with rows at a time. The solution to this problem is Cursors. A cursor is a set of rows together with a set of pointers that help in identifying the current row. Cursors are generally used through stored procedures and triggers. The data retrieved by the cursor is stored in the temdp database.

Working with the cursor is a five step process:

·         Declare Cursor

·         Open

·         Fetch

·         Close

·         Deallocate

Declare Cursor

A cursor must be declared before it is used. The DECLARE CURSOR statement is used to declare the cursor and to set the storage and basic properties of the cursor.

Syntax:

DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR

FOR SELECT_STATEMENTS

[FOR {READ ONLY | UPDATE [OF column_name] } ] 

where 

·         cursor_name : Name of the cursor.

·         INSENITIVE : This is to create a temporary table just for this cursor.

·         SCROLL : It specifies that all the options of the FETCH statement are supported. If SCROLL is Omitted the cursor supports only FETCH NEXT.

·         READ ONLY : It prevents any update through the cursor.

·         UPDATE : It specifically states that the cursor should allow modification.

·         SELECT_STATEMENTS : It is the standard T-SQL statement that is supplying the rows for the cursor. 

Example

DECLARE Product_Cursor CURSOR

for

Select * from ProductTable

where price>=10000 

Open Cursor

Open statement is used to open a cursor before it is used.

Syntax:

OPEN cursor_name

Example

OPEN Product_Cursor

If the cursor was declared with the INSENSITIVE or STATIC keyword then the OPEN statement creates a temporary table in the tempdb database for holding records.These tables are automatically created by the SQL Server and they are also deleted by the the Server once the cursor is closed.

Fetch

FETCH is used to retrieve the data from the cursor into variables so that you can work with the data.

Syntax:

FETCH

[ [ NEXT | PRIOR | FIRST | LAST | ABSOLUTE { n | @n_varaible} | RELATIVE {n | @n_varaible} ]

FROM ]

{ cursor_name }

[ INTO @variable_name1, @variable_name2 … ] 

Example
DECLARE @PRODUCTNAME varchar(50)
DECLARE Product_Cursor CURSOR
for
Select PRODUCTNAME from ProductTable
where price>=10000
 
OPEN Product_Cursor
 
FETCH NEXT FROM Product_Cursor
INTO @PRODUCTNAME
Print @PRODUCTNAME
WHILE @@fetch_status = 0
BEGIN
      FETCH NEXT FROM Product_Cursor
      INTO @PRODUCTNAME
      Print @PRODUCTNAME
END
Output

Cursor in SQL Server

  Close Cursor

When the work is over with the cursor then you should execute a CLOSE statement. This statement frees all the rows that are being held by the cursor but it does not destroy the cursor.

Syntax:

CLOSE cursor_name

Example

CLOSE Product_Cursor 

Deallocate

DEALLOCATE statement removes the definition of the cursor from the SQL server.

Syntax:

DEALLOCATE cursor_name

Example

DEALLOCATE Product_Cursor

EXAMPLE
DECLARE @PRODUCTID int,@PRODUCTNAME varchar(50),@PRICE int
DECLARE PRODUCTCURSOR CURSOR
STATIC
FOR
SELECT PRODUCTID,PRODUCTNAME,PRICE FROM ProductTable
OPEN PRODUCTCURSOR
IF @@cursor_rows > 0
BEGIN
      FETCH NEXT FROM PRODUCTCURSOR
      INTO @PRODUCTID,@PRODUCTNAME,@PRICE
      WHILE @@fetch_status = 0
      BEGIN
PRINT 'ID : '+ convert(varchar(20),@PRODUCTID)+', Name :'+@PRODUCTNAME+ ', PRICE : '+convert(varchar(20),@PRICE)
            FETCH NEXT FROM PRODUCTCURSOR
            INTO @PRODUCTID,@PRODUCTNAME,@PRICE
      END
END
 
CLOSE PRODUCTCURSOR
DEALLOCATE PRODUCTCURSOR
Output

Cursor in SQL Server

  Modified On Nov-29-2017 10:59:58 PM

Leave Comment