What is cursor in sql server with example?

Cursor in sql server is used to retrieve set of data from table, loop through each record row by row, and modify the values based on requirements.

DECLARE cursorname CURSOR

FOR selectstatement — like SELECT OrderID,CustomerID FROM Orders

OPEN cursor

FETCH tablerow FROM cursor

Process Fetched Tablerow

CLOSE cursor

DEALLOCATE cursor

Step 1: Declare Cursor

First we need to declare cursor name that will be used in cursor execution

Step 2: Select Statement

Select statement is used to get data from table to process with cursor

Step 3: Open Cursor

This statement is used to open the cursor to process select statement result set

Step 4: Fetch Rows

Once cursor is opened, rows can be fetched from the cursor one by one

Step 5: Close Cursor

Once our process finished we need to close the cursor

Step 6: Deallocate Cursor

We need to deallocate cursor to delete cursor definition and release resources associated with the cursor

 

DECLARE ex_cursor CURSOR

FOR SELECT OrderID,CustomerID FROM Orders

DECLARE @oid INT

DECLARE @cname NVARCHAR(50)

OPEN ex_cursor

FETCH NEXT FROM ex_cursor INTO @oid,@cname

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT  (CAST(@oid AS VARCHAR(5)) + ‘-‘ + @cname)

FETCH NEXT FROM ex_cursor INTO @oid,@cname

END

CLOSE ex_cursor

DEALLOCATE ex_cursor

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s