Ok basically I want to output one long string like this from the database:
ingredients="full fat cream,milk,sorbitol,cocoa,peanuts";
ingredients="avocado,mandarin orange juice, beta-carotene,acidity regulator, vitamin C" ...
in fact I put newlines for readability but everything is one long concatenated line.
Also the ingredients are fictious in this example (the real thing is very long)
Now so far I have a stored procedure that outputs any string of the format
ingredients[N]="a,b,c,d .. "
And that is:
CREATE PROCEDURE sp_DrinkIngredients
SET NOCOUNT ON;
DECLARE @items varchar(1000)
SELECT @items = 'ingredients[' + CAST(@ID As VarChar(2)) + ']="'
SELECT @items = @items + I.Name + ', '
FROM tblIngredients AS I,
tblDrinkIngredients AS DI
WHERE DI.DrinkID = @ID AND I.[ID] = DI.IngredientID
ORDER BY DI.SortOrder
SELECT SUBSTRING(@items,1,LEN(@items)-2) +'";'
All very peachy.
But I want now to concatenated everything from ID's 1 to (the last ID of tblDrinks)
And I am stuck.
I don't know if I should use cursors (websites warns us not to - as it consume resources)
or to build a loop (something I haven't done yet in transact-SQL)
So I was wondering if anyone that has come across something similar knows of a short way.
I was thinking of something in terms of a loop so we could concatenate results of
EXEC sp_DrinkIngredients @ID=1
EXEC sp_DrinkIngredients @ID=2
and so on
in one big string.
I rather not use a fixed number but say a MAX(ID) from tblDrinks as the upper limit of the loop.
If anyone knows of a more elegant solution which is recursive - that would be even neater!
But any solution will do - please
thanks in advance.