should I use cursor? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

should I use cursor?

Author

Topic

TomKa_t
Starting Member

4 Posts

Posted - 2005-12-28 : 11:32:47

Hi,
It's the second day I'm trying to solve this problem.
I have a selection from four tables, two of columns looks like:


Dovgan      Rice
Dovgan      Cereals
Nike        Boots
Nike        Jacket
Nike        Cap

I wanna make one more selection to make it look like


Dovgan      Rice, Cereals
Nike        Boots, Jacket, Cap

It's very easy to make through function if I had rights to create table for this. But I cannot that is why I created temporary table #Zapros in Stored Procedure, fill it and trying to get only rows that looks like as I said.

Stored Procedure looks like:


DECLARE @Firm VARCHAR(255), @FirmProduction VARCHAR(1024)
DECLARE @NextFirm VARCHAR(255), @NextFirmProduction VARCHAR(255)
DECLARE cursorFirm CURSOR
FOR
SELECT Firm, FirmProduction
        FROM #Zapros -- this is my temporary table
	ORDER BY Firm
OPEN cursorAgency
FETCH NEXT FROM cursorFirm into @Firm, @FirmProduction 
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM cursorFirm into @NextFirm, @NextFirmProduction
 IF @Firm= @NextFirm
 BEGIN

 update   #Zapros 
 set #Zapros.FirmProduction = #Zapros.FirmProduction +'|' + @NextFirmProduction
 END
 SET @Firm = @NextFirm
END


CLOSE cursorFirm
DEALLOCATE cursorFirm

but something's going wrong and I cannot find where is a mistake. Besides this is my first Cursor. Probably someone can help me with this, or at least suggest me some direction to search in.

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-28 : 12:12:08

The "sticky" on the Newbie page http://sqlteam.com/Forums/forum.asp?FORUM_ID=23

has a link to a solution for this problem http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56058

but I've seen more concise solutions posted elsewhere.

This is a common problem. Someone ought to write a how-to article.

Here's one http://sqlteam.com/item.asp?ItemID=11021

Is that what you're after?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-29 : 00:09:38

Dont use Cursor. Use Function
Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

TomKa_t
Starting Member

4 Posts

Posted - 2005-12-29 : 07:48:57

Thanks to EVERYBODY who tried to help!!! And special thanx to madhivanan from this forum and to Lenok and her husband from my ICQ ContactList.
I adapt the function from sqljunkies for my needs + Lenok_n_her_Husband helped me to create right SELECT'ion.
THANX, Guys!!!
Now I need to add some input parameters to my function and it is ready for use on my asp page.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources