Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2005
    Posts
    92

    Unanswered: collecting values into a variable in a loop

    Dear All,

    Im trying to collect values from a query into a single variable within a loop, like so:

    WHILE condition is true
    BEGIN

    SET @intLoop = @intLoop + 1

    @myString = @myString + , + (SELECT companyName FROM @tblTheseComp WHERE id = @intLoop

    END

    For some reason though the @myString does not collect up the values, but will equal NULL at the end of the loop.

    If however I simple do

    WHILE condition is true
    BEGIN

    SET @intLoop = @intLoop + 1

    @myString = (SELECT companyName FROM @tblTheseComp WHERE id = @intLoop
    END


    Then I get the last value from the query as expected.

    Can anyone explain why this might be?

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Your variable is null initially -
    NULL +'Something' = NULL

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also - you don't need a loop:

    Code:
    DECLARE @myString AS VarChar(1000)
    
    SELECT @myString = ''
     
    SELECT @myString = @myString + ', ' + companyName 
    FROM @tblTheseComp
     
    SELECT @myString = SUBSTRING(@myString, 3, LEN(@myString)-2)
     
    SELECT @myString
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Cool

    This code will handle if any of the companyName is null.
    Code:
    DECLARE @myString AS VarChar(1000)
    SELECT @myString = ''
     
    SELECT @myString = @myString + coalesce(companyName+',','')
    FROM @tblTheseComp
     
    SELECT @myString = SUBSTRING(@myString, 1, LEN(@myString)-1)
     
    SELECT @myString
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Mar 2005
    Posts
    92
    Nice one thanks a lot! A final obsticle and I'm there!

    As I need to send a list of action items to my users, I need to add a carriage return at the end of each line, any idea how I would do that, the mail is sent as a text file so I assume I'll need the CTRL + M combination or in ASCII:

    Char: CR, Oct:15, Dec:13, Hex:d

    Any ideas anyone?

  6. #6
    Join Date
    Mar 2005
    Posts
    92
    Hmm wasnt that tricky I needed CHAR(13) + CHAR(10) cool, thanks for all the help again!

  7. #7
    Join Date
    Jun 2003
    Posts
    269
    add char(13) with ur string.
    eg:
    Code:
    PRINT 'First line.' + CHAR(13) + CHAR(10) + 'Second line.'
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  8. #8
    Join Date
    Mar 2005
    Posts
    92
    Thanks yes I noticed I had to include a string to add CHAR(13) + CHAR(10) to. Cheers!

Posting Permissions

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