I am writing some stored procedures in SQL2000. I don't like T-SQL much but nothing I can do about it :)

This is a complex procedure for a complex C# 2.0 (ASP.NET) program.

To simplify the question, say I have a table with a key and various value fields, like this sample:

Key int,
Val1 int,
Val2 int,
val3, int,
--
--
val40 int

The actual table has a ton of columns with weird names. I did not create the table, nor the program, just inherited and must finish it what somebody else has left.

I created a lookup cross-ref table that with some complex code gets me to find what Val..n do I need at any point.
Something like:

DECLARE	@Val1 INT, @Val2 INT, @Val3 INT, @Val4 INT, @Val5 INT

SELECT @Val1=Val1, @Val2=Val2, @Val3=Val3, @Val4=Val4, @Val5=Val5  -- and a lot more
FROM table WHERE -- condition

If I am now passsed a parameter: @Column2Get CHAR(20)
I need to rerwite the query to just get (the equivalent of VFP's)

SELECT @(whatever_var)=(whatever_column) FROM table WHERE -- condition

Sure, I could do an IF.ELSE
like:

IF @Column2Get='Val1'
BEGIN
  SELECT @Val1=Val1 FROM table WHERE -- condition
END
ELSE
BEGIN
  IF @Column2Get='Val1'
  BEGIN
    SELECT @Val2=Val2 FROM table WHERE -- condition
  END
  ELSE
  -- another 40 times!
END

Is there a more elegant solution to build the query on the fly, retrieving the proper column, in a stored procedure?

TIA