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
 SQL Server Development (2000)
 Cursor-less SP, best solution?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lmendoza
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-
leo

in case you're wondering, here's my implicit cursor based sp:
CREATE PROCEDURE usp_newsku
AS

CREATE TABLE #t
(
pk INT
IDENTITY(1,1),
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)
BEGIN
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 = ' '
END


SELECT * FROM #t
DROP TABLE #t


AjarnMark
SQL Slashing Gunting Master

USA
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
quote:

(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

lmendoza
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

robvolk
Most Valuable Yak

USA
15676 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

JustinBigelow
SQL Gigolo

USA
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.

http://www.sqlteam.com/item.asp?ItemID=2368

Justin

Go to Top of Page

izaltsman
A custom title

USA
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
UNION ALL
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
FROM
#temp AS t
INNER JOIN
(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

DROP TABLE #temp

SELECT * FROM #temp2

DROP TABLE #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.

Ilya

Edited by - izaltsman on 07/18/2001 19:39:36
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.14 seconds. Powered By: Snitz Forums 2000