get the value of variable name stored in SQL Sproc

Question

This is what I am trying to do

  
Declare @Var varchar(50)
Declare @Num1 varchar(50)
Declare @Num2 varchar(50)
Declare @Counter smallint

Set @Counter=1
Set @Num1='Hello'
Set @Num2='Hi'

while (@Counter<2)
 begin
   Set @Var=N'@Num'+convert(varchar,@Counter)
   //Now I want to get the value of '@Num1' that is stored in @Var when @Counter=1
   //Help Needed
 Set @Counter=@Counter+1
 end

Answer 1

AFAIK, you can't do that directly; anything wrong with something like:

SET @Var = SELECT CASE @Counter WHEN 1 THEN @Num1 ELSE @Num2 END

If you have a lot of values, consider using a #table (temp table) or @table (table variable) - i.e.

DECLARE @Data TABLE ([Key] int NOT NULL, [Value] varchar(50))

then just INSERT/UPDATE into @DATA

SELECT @Var = [Value] FROM @Data WHERE [Key] = @Counter

Answer 2

The value of @Num1 will be always 'Hello'. The reason for that is that you are storing just a string into @Var.