Cursor in SQL Server

Question

Hi friends, I want to know about the cursor and what are the types of cursor?

Kunal Vaishya · Answer

Dear Cursor use for to get on by one record from Collection loop like for loop ,while loop so Manny examples try this Simple Example of Cursor using AdventureWorks Database is listed here. USE AdventureWorks 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 More reference please refer http://blog.sqlauthority.com/2008/03/05/sql-server-simple-example-of-cursor-sample-cursor-part-2/

Satyapriya Nayak · Answer

Hi Ravi, Cursors allow row-by-row prcessing of the resultsets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors. Most of the times, set based operations can be used instead of cursors. Please refer the below link for the example of Types of cursors http://www.dotnet-tricks.com/Tutorial/sqlserver?id=RLID060512&title=SQL%20Server%20-%20Types%20of%20Cursors Thanks

Cursor in SQL Server

Answers (2)