Home > DeveloperSection > Articles > Cursor in SQL Server

Cursor in SQL Server


Database Database 
Ratings:
0 Comment(s)
 2677  View(s)
Rate this:

Cursor in SQL Server

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


Don't want to miss updates? Please click the below button!

Follow MindStick