What is Cursor in SQL Server with Example

Introduction:

Here I will explain what is cursor in sql server with example or simple cursor example in sql server and how to declare and use cursor in sql server. 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.

Description:

In previous articles I explained While loop example in sql server, nested while loop example in sql server, get only month and year from date in sql server, substring function in SQL server and many articles relating to SQL server. Now I will explain cursor in sql server with example.

To use cursor in sql server that syntax will be like as shown below

Syntax to Create Cursor

DECLARE cursorname CURSOR FOR selectstatement -- like SELECT OrderID,CustomerID FROM Orders OPEN cursor FETCH tablerow FROM cursor Process Fetched Tablerow CLOSE cursor DEALLOCATE cursor

If you observe above syntax we have different steps in 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

We will check this with example for that first create one table UserDetails in your database and insert some 100 or 200 records or install northwind database in your server and use orders table it contains more than 500 records

Example

Write cursor script like as shown below and run it.

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

Output:

When we run above query our results like as shown below

Demo

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email