use cursor or what?

Question

Post reply

use cursor or what?

TomKat

SSC Veteran

Points: 252

December 28, 2005 at 9:49 am

#167556

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

G'Day,

Here is an example that uses coalesce and does not use cursors. This is an example to show you might approach the problem. It is not a proper production grade solution. Hope this helps.

Wayne

DROP TABLE DetailTable

CREATE TABLE DetailTable (

Firm VARCHAR(25),

Product VARCHAR(25)

)

GO

INSERT INTO DetailTable (Firm, Product)

SELECT 'Dovgan', 'Rice' UNION

SELECT 'Dovgan', 'Cereals' UNION

SELECT 'Nike', 'Boots' UNION

SELECT 'Nike', 'Jacket' UNION

SELECT 'Nike', 'Cap'

GO

DROP TABLE MyResults

GO

CREATE TABLE MyResults (

Firm VARCHAR(25),

ProductList VARCHAR(2000)

)

GO

INSERT INTO MyResults (FIRM)

SELECT DISTINCT FIRM FROM DetailTable

GO

DECLARE @ProductList VARCHAR(200),

@MinFirm VARCHAR(25),

@MaxFirm VARCHAR(25)

SELECT @MinFirm = MIN(FIRM),

@MaxFirm = MAX(FIRM)

FROM MyResults

WHILE @MinFirm <= @MaxFirm

BEGIN

SET @ProductList = NULL

SELECT @ProductList = COALESCE(@ProductList + ', ', '') + Product

FROM DetailTable

WHERE FIRM = @MinFirm

UPDATE MyResults

SET ProductList = @ProductList

WHERE Firm = @MinFirm

SELECT @MinFirm = MIN(FIRM)

FROM MyResults

WHERE FIRM > @MinFirm

END

GO

SELECT * FROM MyResults

GO

DCPeterson SSCoach Points: 15266 More actions · Answer 2

The problem is that you are basically "rolling your own" cursor by using the WHILE loop. Yes, you should avoid cursors if possible, but that advice should be broadened to say something like avoid row at a time operations if possible. If it isn't possible to avoid them, then use cursors, they will typically be faster and offer better transactional consistency than the "roll your own" approach.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

TomKat SSC Veteran Points: 252 More actions · Answer 3

Thanks, I've found right decision (using function instead of cursor). Everything's work great.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 4

It would be real nice if you posted the function that work's so great... thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomKat SSC Veteran Points: 252 More actions · Answer 5

Choose the one that you need here:

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

after using one of the functions (see on the page, which address is above) I received the following temporary table named #QueryTable in SP:

Dovgan Rice,

Dovgan Rice, Cereals

Nike Boots,

Nike Boots, Jacket,

Nike Boots, Jacket, Cap

Then used something like this:

select tbl1.* FROM #QueryTable tbl1

INNER JOIN

(select FirmID, max(LEN(FirmProduction)) as MaxLength FROM #QueryTable GROUP BY FirmID) z2 on

tbl1.FirmID=z2.FirmID AND LEN(tbl1.FirmProduction) = z2.MaxLength

Everything works great.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 6

Ok, thanks... you sure you wanted the duplication in the #QueryTable? Could get pretty nasty if you have lots of rows...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomKat SSC Veteran Points: 252 More actions · Answer 7

Of course I don't want it. But I don't know how to "avoid from selecting the dupes in select statement (I mean my case, I'm making selection into #QueryTable joining several tables and cannot modify selection to prevent dupes). But if U know and would be so kind to give me an advice how to do that, I'll try. At least I need a direction to search for the right answer.