Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    26

    Unanswered: Loop through fields in a table, add to variable.

    Hi all,
    can anyone help me with the following problem?
    I am trying to build up a string from values from a field in a table. The variable MyValInt should take the value from MyField and loop through the table, adding a comma and a space each time to create a string at the end which displays all the values in one string. I will then use len, left to shave the last two characters off at the end to tidy it up.
    The code I have so far is below, when I run it it states it has completed successfully but does not display the end result so i'm not sure what it is doing.

    Cany anyone please assist?


    DECLARE @MyValInt int
    DECLARE @MyValStr as nvarchar(255)

    while @MyValInt is not null
    BEGIN
    SELECT @MyValInt = MyField
    FROM tblMyTable
    SET @MyValStr = @MyValInt + ', ' + @MyValInt
    PRINT @MyValStr
    END

    GO

    Thanks,
    Mitch.......
    Last edited by VegaLA; 05-06-10 at 13:48.

  2. #2
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    First, a local variable is set to null when declared thus the loop never executes. Initialize a variable to a default value before using it.

    However, your code does not do what you are expecting and will not work.

    Although the following works the @Values string variable may not be large enough to hold all the field values from the table.

    Code:
    Declare @Values varchar(8000)
    
    Set @Values = ''
    
    Select @Values = @Values + ', ' + Cast(MyField as varchar) From tblMyTable
    
    Print @Values

  3. #3
    Join Date
    Sep 2006
    Posts
    26
    Thanks Bruce.

    The issue i'm facing now is that 'Myfield' needs to be set as a int before it is placed into the string or the value of the field is 3.00111e+006.

    I have been playing around with the code but no joy so far.
    I'm wondering if it is possible to brign in MyField as an int, assign it to the first variable then use that to add the comma and the next value before setting it to the second variable which is then a string DataType?

    Thanks again for your help, i'll keep you posted if I crack it.

  4. #4
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    What is the Datatype of the MyField field?

    Regardless, look at using Convert instead of Cast. With Convert you can specify a Format when casting numeric values to a string.

    You could also use Cast multiple times.

    Select @Values = @Values + ', ' + Cast(Cast(MyField as int) as varchar) From tblMyTable

  5. #5
    Join Date
    Sep 2006
    Posts
    26
    It's a Numeric. However I'm pulling that value from a view and what I have done is cast it in the view. Using the code you posted for me I now get it to string just as I planned.

    Problem now is that the DTs package won't read it as a Global Variable but thats a whole other issue.

    Thanks again so much for your help!

Posting Permissions

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