transactSQL Loop - any fast way of doing this?

[hgilbert]

Ok basically I want to output one long string like this from the database:

"ingredients[0]="banana,milk,apple,sugar,honey,flavourings";
ingredients[1]="lemon,water,pear,sugar,flavourings";
ingredients[2]="full fat cream,milk,sorbitol,cocoa,peanuts";
ingredients[3]="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
(@ID int)
AS
BEGIN
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) +'";'

END

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

[Guimauve]

Couple of questions:

1. Does your stored procedure return the concatenated values as an output parameter or as a row?
2. Are your IDs numeric?
3. How do you get the list of IDs that you want to process?
4. You say you want one very long string, do you think you will reach the limit of 8000 characters that SQL Server imposes?
5. Can you live with a result set of rows instead, where each row has the ID and the concatenated string of ingredients?

Charles

[Guimauve]

OK, I think the easiest thing would be to use a function to do the work instead of a stored procedure. Take the following example:

The Function:

CREATE FUNCTION GetIngredients
  (
  @ID AS Integer
  )
RETURNS VARCHAR(2000)
BEGIN

DECLARE @Ingredients AS VARCHAR(2000)

SET @Ingredients= 'ingredients[' + CAST(@ID As VarChar(2)) + ']="'

SELECT @Ingredients = @Ingredients + RTRIM(I.Name) + ','
  FROM tblIngredients AS I, 
       tblDrinkIngredients AS DI 
 WHERE DI.DrinkID = @ID
   AND I.[ID]     = DI.IngredientID  
 ORDER BY DI.SortOrder

SET @Ingredients = SUBSTRING(@Ingredients , 1, LEN(@Ingredients ) - 1)

RETURN @Ingredients 
END

Use the function in the following way:

SELECT ID,
   dbo.GetIngredients(ID)
FROM tblDrinks
 WHERE ...

Charles

[hgilbert]

Hi thanks very much for your reply.
I am still not acquainted to functions.
I tried that route and gave up - then I tried to think "VB"
surely there must be a loop way of doing things.

After some trial and error I believe I cracked it.
But this lil exercise has been bugging me for over a week.

I put a test.asp page up and it seems to work fine.

CREATE PROCEDURE [sp_JSArrayIngredients] AS
DECLARE @array varchar(3000)
DECLARE @elements varchar(500)
DECLARE @counter int
DECLARE @max int


SELECT @max = MAX(ID) FROM tblDrinks
SET @counter = 0

SELECT @array = 'var ingredients = new Array(""'

WHILE(@counter < @max)
BEGIN
SET @counter = @counter + 1
SELECT @elements = ',"'
SELECT @elements = @elements + I.Name + ', '
FROM tblIngredients AS I,
tblDrinkIngredients AS DI
WHERE DI.DrinkID = @counter AND I.[ID] = DI.IngredientID
ORDER BY DI.SortOrder
SELECT @array = @array + SUBSTRING(@elements,1,LEN(@elements)-1) +'"'
END

SELECT @array + ');'

TransactSQL is so damn ugly
Of course C# is even worse -
so I rather keep the logic in the database as much as possible