Just another day I heard a senior DBA discussing advantages and disadvantages of the cursor. Well, personally I am a firm believer of the set based operations. However, there are few places where cursors are the only solutions. It is quite possible that different experts have different opinion about cursor; some love cursor and some hate it but definitely user can’t ignore cursors.
If you are ever asked to write a cursor, you can just write cursor based on following a script.
USE AdventureWorks2014
GO
DECLARE @ProductID INT
DECLARE @getProductID CURSOR
SET @getProductID = CURSOR FOR
SELECT ProductID
FROM Production.Product
OPEN @getProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ProductID
FETCH NEXT
FROM @getProductID INTO @ProductID
END
CLOSE @getProductID
DEALLOCATE @getProductID
GO
You can read about cursor examples over here Simple Example of Cursor – Sample Cursor Part 2.
Here are few additional examples of cursors:
- SQL SERVER – Simple Example of Cursor
- SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database
- SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created
- SQL SERVER – Cursor to Kill All Process in Database
Cursor uses WHILE keyword to loop over variables. If you are working with local variable you can also use while loop. Here is an example of WHILE loop where the variable is incremented by 1 at every iteration.
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
PRINT @intFlag
SET @intFlag = @intFlag + 1
END
GO
You can read about this over Simple Example of WHILE Loop With CONTINUE and BREAK Keywords.
This is just a simple example of cursor, in reality there are quite a few different options of the cursor as well. We will discuss this in future blog posts.
Reference: Pinal Dave (https://blog.sqlauthority.com)
