can cursor be nested?

Hi, I'm a newbie on using cursor. My question is if I create an update
trigger that loop through the Inserted rows by a cursor, and inside the
cursor loop, a stored procedure is executed, which contains a cursor loop
too, any problem about it?YEs of course, you can also nest cursors inline.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
news:8EFFAC69-6544-4923-8A41-F228688A2F93@.microsoft.com...
> Hi, I'm a newbie on using cursor. My question is if I create an update
> trigger that loop through the Inserted rows by a cursor, and inside the
> cursor loop, a stored procedure is executed, which contains a cursor loop
> too, any problem about it?|||Thx for ur reply :) But if the outer cursor loop and the inner cursor loop
both access the same table, will deadlock occur?
"Jens Sü?meyer" wrote:

> YEs of course, you can also nest cursors inline.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:8EFFAC69-6544-4923-8A41-F228688A2F93@.microsoft.com...
>
>|||Have a look at the Cursor option in BOL, you can handle the outside cusors
to behave as readonly if you need to. Rember that the default locking
beahviour is row locking, so even you will lock the data with anyother
option it depends on the the option wheter you lock one or multiple
datarows.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
news:9D8BD362-E3F9-4E1A-80BF-819CD58B2D85@.microsoft.com...
> Thx for ur reply :) But if the outer cursor loop and the inner cursor loop
> both access the same table, will deadlock occur?
> "Jens Smeyer" wrote:
>|||another question:
if I open a cursor in a transaction and the transaction rollback, will the
cursor be automatically closed and deallocated?
"Jens Sü?meyer" wrote:

> Have a look at the Cursor option in BOL, you can handle the outside cusors
> to behave as readonly if you need to. Rember that the default locking
> beahviour is row locking, so even you will lock the data with anyother
> option it depends on the the option wheter you lock one or multiple
> datarows.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:9D8BD362-E3F9-4E1A-80BF-819CD58B2D85@.microsoft.com...
>
>|||Because of the termination of the session inthat case, that would be the
effect.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
news:C8DF1B00-5A10-413D-83A8-2986CBE6B4A2@.microsoft.com...
> another question:
> if I open a cursor in a transaction and the transaction rollback, will the
> cursor be automatically closed and deallocated?
> "Jens Smeyer" wrote:
>|||So to keep up your questions, it depends...
The inner Cursor will be closed when the session ends, the session ends when
the whole logic block is executed or an serverity error occured that kept
SQl Server from continuing the Cursor and the Transaction is ended, that the
fact if you call an procedure in the outer cursor which build up a cursor i
the prcedure)
Its easy to recode if you just write a simple cursor which call a procedure
and this sp establish a cursor which run into an error, try to declare the
cursor with the same name now from the QA.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"nonno" <nonno@.discussions.microsoft.com> schrieb im Newsbeitrag
news:C8DF1B00-5A10-413D-83A8-2986CBE6B4A2@.microsoft.com...
> another question:
> if I open a cursor in a transaction and the transaction rollback, will the
> cursor be automatically closed and deallocated?
> "Jens Smeyer" wrote:
>|||To add to the responses by Jens, it is often possible to use a set-based
processing rather than cursors. The set-based approach usually provides
better performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:8EFFAC69-6544-4923-8A41-F228688A2F93@.microsoft.com...
> Hi, I'm a newbie on using cursor. My question is if I create an update
> trigger that loop through the Inserted rows by a cursor, and inside the
> cursor loop, a stored procedure is executed, which contains a cursor loop
> too, any problem about it?|||But why would anyone write code like that in SQL? It is a XXXXX to
maintain, proprietary and each cursor is 1 to 2 orders of magnitude
slower than declarative SQL.
Post the DDL and a statement of the problem and you can get a better
answer.