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.
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 |
|
|
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 |
|
|
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? |
|
|
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 CURSORREAD_ONLYFOR SELECT au_fname FROM pubs.dbo.authorsDECLARE @name varchar(40)OPEN names1FETCH NEXT FROM names1 INTO @nameWHILE (@@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 @nameENDCLOSE names1DEALLOCATE names1GO MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2004-07-21 : 23:05:30
|
Thanks! |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-21 : 23:11:27
|
No problem Señor.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|