Interview Question of the Week #003 – How to Write Script for Database Cursor?

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:

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)

Previous Post
SQL SERVER – Watching Table Variable Data in TempDB
Next Post
SQL SERVER – How to Find Weak Passwords Using T-SQL?

Related Posts

No results found.

Leave a Reply

Menu