SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru hgilbert's Avatar
    Join Date
    Dec 2004
    Location
    London
    Posts
    839
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    transactSQL Loop - any fast way of doing this?

    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.

  2. #2
    SitePoint Addict Guimauve's Avatar
    Join Date
    Aug 2004
    Location
    Chicago
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Addict Guimauve's Avatar
    Join Date
    Aug 2004
    Location
    Chicago
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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:
    Code:
    SELECT ID,
       dbo.GetIngredients(ID)
    FROM tblDrinks
     WHERE ...
    Charles

  4. #4
    SitePoint Guru hgilbert's Avatar
    Join Date
    Dec 2004
    Location
    London
    Posts
    839
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •