SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 nested cursor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 07/21/2004 :  20:00:32  Show Profile  Reply with Quote
can someone show me some examples of nested cursor?



AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 07/21/2004 :  20:07:49  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/21/2004 :  20:40:35  Show Profile  Reply with Quote
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

USA
503 Posts

Posted - 07/21/2004 :  22:06:13  Show Profile  Reply with Quote
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

USA
4184 Posts

Posted - 07/21/2004 :  22:50:28  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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

USA
503 Posts

Posted - 07/21/2004 :  23:05:30  Show Profile  Reply with Quote
Thanks!





Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/21/2004 :  23:11:27  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
No problem SeƱor.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000