Change variable name within loop

[LiLMsNinja]

Soo.. is there any way in the world to change a variable name within a loop using the number stored in the count variable. Similiar to:

SELECT @CNT = 1
WHILE @CNT <= 3
BEGIN

-- THE LVL1 VARIABLE NEEDS TO HAVE THE NUMBER 1 CHANGE AS
-- THE NUMBER STORED WITHIN @CNT CHANGES

SELECT @LVL = LVL1 FROM @FINALSECTION
	WHERE ROWNUM = @RowCnt

SELECT @STR = @STR + (SELECT hierarchy_number FROM	DATABASE.dbo.Hierarchy
             WHERE hierarchy_id = @LVL)


SELECT @STR = @STR + (SELECT hierarchy_NAME FROM	DATABASE.dbo.Hierarchy
            WHERE hierarchy_id = @LVL)


SELECT @CNT = @CNT + 1
END

I've tried numerous combinations and nothing is working.

I would also like to put a dash between each 'hierarchy_number' and 'hierarchy_name' that i pull. Similiar logic as:

SELECT @STR = @STR + (SELECT hierarchy_number FROM 			DATABASE.dbo.Hierarchy					WHERE hierarchy_id = @LVL) + ' - '


SELECT @STR = @STR (SELECT hierarchy_NAME FROM 				DATABASE.dbo.Hierarchy					WHERE hierarchy_id = @LVL) + ' - '

But that gives an 'Incorrect syntax near ' - '. But only for the second hyphen. WTF!!

[LiLMsNinja]

haha.. i'm missing a ' + ' in the second @STR... Where i grab the 'hierarchy_name'. Wow..

But, still need to figure out how to increment the variable

[zeroradius]

have you tried seting the veriable name to a veriable and then concantanating cnt on to it than using that veriable in place of the veriable you want to have change in the query?

I don't even know what language that is your using but to illistrate what i mean i will show you in php with mysql.

$i=1;
while($i<=5)
  {
  $newVar = "oldVar" . $i;
   $blah = "Select * from $newvar";
  $i++;
  }
?> 

The above syntex won't run but should give you anidea of what i am talking about.