cursor application?

[Sam]

Hi all,
This is not an urgent query merely an interested question. But for
what would I use a Cursor for? I have looked at the on-line help but I
am still unclear why it for what reason it might be employed?
Many thanks
Sam

[Simon Hayes]

Re: cursor application?

Two reasons would be when you need to do something for each row in a
result set (usually execute a stored procedure), or when you must
process rows in a specific order.

The first case is probably most common in DBA/admin scripts, for
example to write a script which GRANTs execute permission on all stored
procs in a database. I can't think of a good example of the second case
offhand.

You might want to check out this book, which has a chapter on using
cursors:

http://www.sql.co.il/books/advtsql/Advanced%20T-SQL%20for%20SQL%20Server%202000.htm

But notice the chapter title ("Server-Side Cursors -- the SQL of Last
Resort") - using cursors in application code (as opposed to
DBA/management code) is rather unusual, and generally not at all
desirable.

Simon

[chrisandkayenolan@gmail.com]

Re: cursor application?

Cursors are a bad idea to use as they hold locks on tables for their
duration. They are also not necessary as you can use set rowcount 1 to
loop through records individually.

Simon Hayes wrote:[color=blue]
> Two reasons would be when you need to do something for each row in a
> result set (usually execute a stored procedure), or when you must
> process rows in a specific order.
>
> The first case is probably most common in DBA/admin scripts, for
> example to write a script which GRANTs execute permission on all stored
> procs in a database. I can't think of a good example of the second case
> offhand.
>
> You might want to check out this book, which has a chapter on using
> cursors:
>
> http://www.sql.co.il/books/advtsql/A...ver%202000.htm
>
> But notice the chapter title ("Server-Side Cursors -- the SQL of Last
> Resort") - using cursors in application code (as opposed to
> DBA/management code) is rather unusual, and generally not at all
> desirable.
>
> Simon[/color]

[Chandra]

Re: cursor application?

Hi
Cursors are mainly used to traverse reach row in the result of a query.

If u would like to check each row and perform a certain operation on
that, u use a cursor. For example u can check for hierarchical queries
in BOL

best Regards,
Chandra

http://groups.msn.com/SQLResource/

http://chanduas.blogspot.com/

---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

[Sam]

Re: cursor application?

sorry but why not use 'where' or 'if' to check each row?

[David Portas]

Re: cursor application?

Exactly. Chandra's statement is a bit ambiguous. Most of the time you
*can* conditionally perform an operation for each row using a WHERE
clause or using some combination of a WHERE clause and CASE
expressions. Most of the time that is a better option than using a
cursor. The situations where you cannot do it with set-based DML
statements (SELECT, UPDATE, DELETE, INSERT) are usually to do with
iterative operations that cannot feasibly be defined declaratively -
for example the problem of expanding an adjacency list hierarchy to an
unknown depth. In those cases you may find that a cursor or client-side
code are the optimum solutions.

The other sensible application for a cursor is the one Simon mentioned
- administrative and management processes that need to execute non-data
manipulation code for each row in a set - for example performing
backups, loading files, sending emails. For data manipulation
operations, however, 99% of the time there are better solutions that
don't require cursors.

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

Re: cursor application?

(chrisandkayeno lan@gmail.com) writes:[color=blue]
> Cursors are a bad idea to use as they hold locks on tables for their
> duration. They are also not necessary as you can use set rowcount 1 to
> loop through records individually.[/color]

SET ROWCOUNT 1 is probably the worst form of iteration you can do. That
was what we did back in 4.x days, and it was no fun at all. When you
need to iterate, cursors are indeed the way to do.

Whether cursors hold locks or not, depends on what sort of cursor
you use. The default cursor type is a keyset cursor, a creature I
have never fully understood. Add INSENSITIVE before CURSOR, and you
don't have to worry. The result set for the cursor is fixed once
for all.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp