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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Cursor-less SP, best solution?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

12 Posts

Posted - 07/18/2001 :  13:07:52  Show Profile  Reply with Quote
Hey all -
I'm started using SQL Server about 6 months ago, and I was wondering if you could give me some help making this stored proc. The data is designed like so

new_sku: (about 4000 rows)
sku int

longdesc: (about 600,000 rows)
ace_sku int
sequence_number int
bullet_text nvarchar(64)

it's a 1:N relationship for sku:longdesc.
I need to create one table containing the skus and the bullet_text, in the correct sequence (why we're creating one table is beyond me, since this schema is better, but I digress).

so ex:
sku: longdesc
1 1 1 bullet1
1 2 bullet2
1 3 bullet3

will make the table
1 bullet1*bullet2*bullet3

My coworker designed a explicit cursor based solution, which took about 4 hours to process 160 rows, so I took it upon myself to design another solution. The only one I came up with uses an implicit cursor. I'm convinced that there is a way to do this without using any cursors, though...Am I right?

thanks in advance-

in case you're wondering, here's my implicit cursor based sp:

pk INT
sku INT,
descr NVARCHAR(2048)

INSERT INTO #t (sku)
(SELECT sku FROM new_sku)

DECLARE @cnt INT, @max INT, @sku NVARCHAR(20)
DECLARE @desc nvarchar(1048)

SET @cnt = 0

SELECT @max = MAX(pk) FROM #t

--start the loop
WHILE (@cnt <= @max)
SET @cnt = @cnt + 1
SELECT @sku = sku FROM #t WHERE pk = @cnt --get the sku

--infuse the descriptions
SELECT @desc = COALESCE(@desc + ' ',' ') + LTRIM(RTRIM(bullet_text))
FROM longdesc WHERE ace_sku = @sku

--update the temp table
UPDATE #t SET descr = @desc WHERE pk = @cnt
SET @desc = ' '


SQL Slashing Gunting Master

3246 Posts

Posted - 07/18/2001 :  14:03:27  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
You need to go back to your parenthetical comment

(why we're creating one table is beyond me, since this schema is better, but I digress).

Why ARE you doing it? If you don't know why you are doing it, it probably shouldn't be done.

Go to Top of Page

Starting Member

12 Posts

Posted - 07/18/2001 :  14:41:24  Show Profile  Reply with Quote
Well - it needs to be done this way because it works better with the legacy code that references just one table, instead of the better, normalized two. I'm currently in the process of trying to get a redesign done, and this will help my case.

Anyhow - If anyone could offer some advice on optimizing this SP, i would really appreciate it. It's been running for about a hour now, and I still haven't gotten any debug printouts, let alone results. (my print statements are within the while loop)

Go to Top of Page

Most Valuable Yak

15732 Posts

Posted - 07/18/2001 :  14:52:38  Show Profile  Visit robvolk's Homepage  Reply with Quote
Why not create a view of the data instead of redoing the tables, and have the legacy code reference the view instead?

(I don't actually think it's possible, but it's worth a shot)

I don't think you'll do better with the SP than what you've got now. You might want to email Garth (Wells), he wrote the COALESCE article on CSV values, he might have a trick or two to fix it up.

Go to Top of Page

SQL Gigolo

1157 Posts

Posted - 07/18/2001 :  14:54:14  Show Profile  Reply with Quote
Here is a link to an article that garth wrote concerning what you are trying to do, it maybe of help.


Go to Top of Page

A custom title

1139 Posts

Posted - 07/18/2001 :  19:15:48  Show Profile  Send izaltsman an AOL message  Send izaltsman an ICQ Message  Reply with Quote
Don't know if this will be any faster than a cursor, but here is a set-based solution...
Test it out (I didn't have much test data to go on, but I think it should work).

SELECT sku, sequence_number, bullet INTO #temp FROM
SELECT ace_sku as sku, sequence_number, bullet_text as bullet FROM longdesc
SELECT sku, -1 as sequence_number, CAST(NULL AS NVARCHAR(4000)) as bullet
FROM new_sku
)as x
ORDER BY sku, sequence_number

DECLARE @result NVARCHAR(4000)
UPDATE #temp
SET @result=bullet = COALESCE (@result+bullet+' ', bullet+' ', '')

SELECT IDENTITY(int) as pk, t.sku, t.bullet as descr INTO #temp2
#temp AS t
(SELECT sku, MAX(sequence_number) AS sequence_number FROM #temp GROUP BY sku) as keepers
ON t.sku = keepers.sku AND t.sequence_number = keepers.sequence_number
ORDER BY t.sequence_number


SELECT * FROM #temp2


I assumed that skus are unique within a table and that sequence numbers are unique within a sku. Also assumed that there are no negative sequence numbers.


Edited by - izaltsman on 07/18/2001 19:39:36
Go to Top of Page
  Previous Topic Topic Next 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.03 seconds. Powered By: Snitz Forums 2000