EXEC Command Problem In Sp

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??


How can i get the value from exec clause to @variable ??


Many thanks,