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.
| Author |
Topic |
|
lmendoza
Starting Member
12 Posts |
 Posted - 2001-07-18 : 13:07:52
|
| 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 sonew_sku: (about 4000 rows) sku intlongdesc: (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: longdesc1 1 1 bullet1 1 2 bullet2 1 3 bullet3will make the table1 bullet1*bullet2*bullet3My 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-leoin case you're wondering, here's my implicit cursor based sp:CREATE PROCEDURE usp_newskuASCREATE 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 = 0SELECT @max = MAX(pk) FROM #t--start the loopWHILE (@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 = ' 'ENDSELECT * FROM #tDROP TABLE #t |
|
|
|
|
|
|
|
Cursor-less SP, best solution?