[thelist] SELECT @Variable FROM Table

[Darren]

Hi all,

I'm using SQL Server 2K and *trying* to select a value from a table where
the column name is held in a variable.  Is there any way to do this??

For example, if you have a table like:

   create table #Test (
      ind       integer   identity,
      jan       integer,
      feb       integer,
      mar       integer,
      ...
      nov       integer,
      dec       integer
   )

I can get the value for March and assign it to a variable with a simple

   select @Variable = mar from #Test where ind = 1

(I can do that bit! :>), but if the column name is also stored in a
variable, I can't seem to make the assignment.

I've tried:

   exec('select @Variable = ' + @MonthName + ' from #test where ind = 1')

which gives a 'must declare variable' error

and

   set @Variable = exec('select ' + @MonthName + ' from #test where ind = 1')

which give an 'error near exec' error.

and various permutations of the above.

Has anyone any ideas how to do this??

Many thanks,

Darren