An alternative to using the sp_executesql:

This assumes @Agedstatement is something like '> 30', @Clearstatement is ' AND IsCleared = 1', etc. The parantheses are important.

EXEC ('SELECT * FROM #Final WHERE Aged ' + @AgedStatement + ' ' + @ClearStatement + ' ' + @IsHeldStatement +
	' Order by RecordType, SUBSTRING(Account, 12, 8), Account, CFI, Aged, SWDN')

>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