SQL Server - cursor in sql - Asked By raju singh on 15-Feb-12 05:37 AM

what is cursor in sql. ans its use
Web Star replied to raju singh on 15-Feb-12 05:44 AM
When we have requirement to iterate one by one record from in result set and lots of businiess logic need to implement with each row of data than we will user cursor in sql server
you can see these link to describe details of cursor and syntax with example how can use it 
http://msdn.microsoft.com/en-us/library/ms180169.aspx 
simple example here
http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/ 
problem based sample
http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/ 
dipa ahuja replied to raju singh on 15-Feb-12 05:45 AM
What is Cursor?

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

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
Sreekumar P replied to raju singh on 16-Feb-12 03:37 AM
Hi,

A database cursor is a control structure that enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. The database cursor characteristic of traversal makes cursors akin to the programming language concept of iterator.

"Please note that cursors are the SLOWEST way to access data inside SQL Server. The should only be used when you truly need to access one row at a time. The only reason I can think of for that is to call a stored procedure on each row.  In the http://www.sqlteam.com/article/cursor-performance article I discovered that cursors are over thirty times slower than set based alternatives."

The basic syntax of a cursor is:

DECLARE @AuthorID char(11)
   
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors
 
OPEN c1
 
FETCH NEXT FROM c1
INTO @AuthorID
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
  PRINT @AuthorID
 
  FETCH NEXT FROM c1
  INTO @AuthorID
 
END
 
CLOSE c1
DEALLOCATE c1

More Ref:
http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/
http://www.databasejournal.com/features/mssql/article.php/1439731/Using-SQL-Server-Cursors.htm
http://www.sqlservercentral.com/articles/Advanced+Querying/2785/