Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 nested cursor

Author  Topic 

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-21 : 20:00:32
can someone show me some examples of nested cursor?



AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-07-21 : 20:07:49
Egad! NO NO NO!!! Don't do it! You don't want to go there. The performance would be horrible! Run away! Run away!

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-21 : 20:40:35
tell us what you are trying to do and we can help you with a set based approach to solve your problem.

Please include DDL for any tables, views, sprocs, etc. that are involved.


-ec
Go to Top of Page

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-21 : 22:06:13
I just want to know the syntax.

The performance is not the issue here.

How do i use a cursor inside of a cursor.

Any example?




Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-21 : 22:50:28
Ok. Here's a cut and paste example from the templates in Query Analyzer. Don't blame me if you use this though. :)



-- =============================================
-- Declare and using a READ_ONLY cursor
-- =============================================
DECLARE names1 CURSOR
READ_ONLY
FOR SELECT au_fname FROM pubs.dbo.authors

DECLARE @name varchar(40)
OPEN names1

FETCH NEXT FROM names1 INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
DECLARE @message varchar(100)
SELECT @message = 'my name is: ' + @name
PRINT @message

-- =============================================
-- Declare and using a KEYSET cursor
-- =============================================
DECLARE names2 CURSOR
KEYSET
FOR SELECT au_fname FROM pubs.dbo.authors

DECLARE @name1 varchar(40)

OPEN names2

FETCH NEXT FROM names2 INTO @name1
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
PRINT 'updating record for ' + @name1
UPDATE pubs.dbo.authors
SET phone = replace(phone, ' ', '-')
WHERE CURRENT OF names2
END
FETCH NEXT FROM names2 INTO @name1
END

CLOSE names2
DEALLOCATE names2


END
FETCH NEXT FROM names1 INTO @name
END

CLOSE names1
DEALLOCATE names1
GO



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-21 : 23:05:30
Thanks!





Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-21 : 23:11:27
No problem Señor.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
   

- Advertisement -