Sql Cursor in TSQL

Sql Cursor in TSQL

In SQL Server the cursor is a tool that is used to iterate over a result set, or to loop through each row of a result set one row at a time. It may not be the best way to work with a set of data, but if you need to loop row by agonizing row (RBAR) in a T-SQL script then a cursor is one way of doing it.

Before creating the cursor, we will just start with a simple query that will end up being used in the cursor.

USE AdventureWorks2008;
GO
 
SELECT BusinessEntityID, Name
 FROM Sales.Store;

 

Which looks something like this:

SimpleCursor1

Now to convert it to a cursor, instead of just a select statement.

Step 1: Declare variables to hold the output from the cursor.

1
2
DECLARE @BusinessEntityID as INT;
DECLARE @BusinessName as NVARCHAR(50);

Step 2: Declare the cursor object;

1
DECLARE @BusinessCursor as CURSOR;

Step 3: Assign the query to the cursor.

1
2
3
SET @BusinessCursor = CURSOR FOR
SELECT BusinessEntityID, Name
 FROM Sales.Store;

Step 4: Open the cursor.

1
OPEN @BusinessCursor;

Step 5: Fetch the first row.

1
FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;

Step 6: Loop until there are no more results.  In the loop print out the ID and the name from the result set and fetch the net row.

1
2
3
4
5
WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@BusinessEntityID as VARCHAR (50)) + ' ' + @BusinessName;
 FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
END

Step 7: Close the cursor.

1
CLOSE @BusinessCursor;

Step 8: Deallocate the cursor to free up any memory or open result sets.

1
DEALLOCATE @BusinessCursor;

Now putting it all together:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE @BusinessEntityID as INT;
DECLARE @BusinessName as NVARCHAR(50);
DECLARE @BusinessCursor as CURSOR;
SET @BusinessCursor = CURSOR FOR
SELECT BusinessEntityID, Name
 FROM Sales.Store;
OPEN @BusinessCursor;
FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@BusinessEntityID as VARCHAR (50)) + ' ' + @BusinessName;
 FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
END
CLOSE @BusinessCursor;
DEALLOCATE @BusinessCursor;

Here a video

Ref:http://stevestedman.com/2013/04/t-sql-a-simple-example-using-a-cursor/

Leave a Reply

Your email address will not be published. Required fields are marked *