Combining the results of a cursor loop

Need a little help here.

I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.

I need the results as one table.

Here is my code.
___________________________________
SET NOCOUNT ON

DECLARE @.IdsString VARCHAR(255), @.Id int

SELECT @.IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @.Id

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @.Id

FETCH NEXT FROM GetData
INTO @.Id
END

CLOSE GetData
DEALLOCATE GetData
_____________________________________

Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?

Any help would be much appreciated.

NB Database was not designed and the client will not tolerate any changes to structure of the tables :eek:

Regards

Shaun McGuileSET NOCOUNT ON
CREATE TABLE #CurrentRates
(
AccountType VARCHAR(50),
EffectiveDate DATETIME,
tier INT,
gross FLOAT,
net FLOAT,
aer FLOAT,
footnotes VARCHAR(2000),
[id] INT
)
GO

DECLARE @.IdsString VARCHAR(255), @.Id int

SELECT @.IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @.Id

WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT #CurrentRates
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @.Id

FETCH NEXT FROM GetData
INTO @.Id
END

CLOSE GetData
DEALLOCATE GetData

SELECT * FROM #CurrentRates

Works, but is it good? ;)

Regards

Shaun McGuile|||Dump the cursor and use a split function so you can do this in a set based fashion.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2
See if you can get your string into a table of rows and then we can move on.

BTW - have you changed your handle? What was it before?|||Works, but is it good? ;) It uses cursors :o|||Dump the cursor and use a split function so you can do this in a set based fashion.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2
See if you can get your string into a table of rows and then we can move on.

BTW - have you changed your handle? What was it before?

The line

...Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s...

Splits the ids into a table of column product id.

Have I understood your question?

Regards|||I have and always will be the one and only Shaun McGuile ;)

Lost my dbforums password/email combination somehow.|||Forgot to add the 'drop table #CurrentRates' at the end of the code

Doh!|||Beg your pardon - I thought you were parsing as a string.

Ok - what's the pk of dbo.GetProductRateView?|||pk ha ha ha ha ha ha ha - er..sorry Pootle you had me there.

The db has no pk's nor integrity of any type its real bad

dbo.GetProductRateView is a View pulling data from three non normalised tables its really evil - your heart and that of other members of the community might not take the shock of seeing them.

Its like 'The Ring' of databases (like the film - you see it then you die) lol.|||Well brace yourself

SELECT TOP 100 PERCENT dbo.saving_product.name AS AccountType, dbo.saving_product_variant.from_date AS EffectiveDate,
dbo.saving_product_variant.tier, dbo.saving_product_variant.gross, dbo.saving_product_variant.net, dbo.saving_product_variant.aer,
dbo.saving_date.footnotes, dbo.saving_product.id
FROM dbo.saving_product INNER JOIN
dbo.saving_product_variant ON dbo.saving_product.id = dbo.saving_product_variant.link_id INNER JOIN
dbo.saving_date ON dbo.saving_product.id = dbo.saving_date.link_id
GROUP BY dbo.saving_product.name, dbo.saving_product_variant.from_date, dbo.saving_product_variant.tier, dbo.saving_product_variant.gross,
dbo.saving_product_variant.net, dbo.saving_product_variant.aer, dbo.saving_date.footnotes, dbo.saving_product.id
ORDER BY MAX(dbo.saving_product_variant.date_id) DESC|||date_id? :S

Are you getting the most recent row based on the value date_id? If so then you should know that order by clauses are not guaranteed to work in views. Better to create a view with no order by clause and order it when required.

From BoL:
The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.|||What version are you running BTW?|||INSERT #CurrentRates
SELECT TOP 1 v.*
FROM ( SELECT * FROM dbo.GetProductRateView Order By date_id desc) as v
WHERE v.[id] = @.Id

and remove the order by clause from the view?

Regards

Shaun McGuile|||You can do - no need for the inner query BTW. I'm thinking more than that though.

What version are you running?|||SQLServer 2000 is the db.|||Heh - turns out I didn't need it - apols.

SELECT v.*
FROM dbo.GetProductRateView as v
INNER JOIN--"Last" date per product.
(SELECT dbo.saving_product.id
, MAX(dbo.saving_product_variant.date_id) AS last_date_id
FROM dbo.saving_product
INNER JOIN
dbo.saving_product_variant
ON dbo.saving_product.id = dbo.saving_product_variant.link_id
INNER JOIN
(SELECT *
FROM dbo.split_function(@.IdsString)) AS ids
ON ids.Value = dbo.saving_product.id
GROUP BY dbo.saving_product.id) AS last_prods
ON last_prods.id = v.id
AND last_prods.last_date_id = v.date_id
How is that for the data?|||I'll give it a go and let you know.

Cheers Pootle.

Haven't looked in on Yak Coral in ages. Might do it today if I get time.|||Yeah that works splen-diddly (its how you say it out loud that gets ya).

Only modifictions I had to make were field name for the productID instead of value, altered the view to return date_id field and a DISTINCT is needed as in
SELECT DISTINCT v.* ...

Bril thats my homework done! On with the next assignment!

lol only joking! I dont do homework!

Cheers Pootle

Shaun McGuile|||Kills the cursor/temp table method on speed

Virtually instant vs 2 - 3 seconds

Amazing!|||Kills the cursor/temp table method on speed

Virtually instant vs 2 - 3 secondsThat's set based programming for you. The other thing to remember is that speed of the cursor will be linear. Each additional iteration will take ~ as long as the last. Set based stuff mitigates against changes in scale much better.