Help Using Variables in MS SQL Statement

Question

I have a stored procedure to which I pass 4 variables, @DCUId,@SlaveAddress,@DateFrom,@DateTo

Within the SP I have the following:

DECLARE @MinAmbient decimal (4,1),@AverageAmbient decimal (4,1)
DECLARE @MaxAmbient decimal (4,1)

SELECT @MaxAmbient = MAX(CAST(T4 AS DECIMAL))/10,
@MinAmbient = MIN(CAST(T4 AS DECIMAL))/10,
@AverageAmbient = AVG(CAST(T4 AS DECIMAL))/10
FROM RECORDEDDETAIL WHERE DCUId = CONVERT(nvarchar(4),@DCUId) 
AND SlaveId = @SlaveAddress
AND timestamp BETWEEN convert(nvarchar(20), @DateFrom,113) 
AND convert(nvarchar(20), @DateTo,113)

which works fine.

I want to make the column value 'T4' a variable which I can pass in ie.

DECLARE @TLink nvarchar(3)
SET @TLink = 'T4'

then do something like

SELECT @MaxAmbient = MAX(CAST(@TLink AS DECIMAL))/10,
@MinAmbient = MIN(CAST(T4 AS DECIMAL))/10,.....
.......... etc

When I do this I get an error ...

What am I doing wrong?

Thanks

Roger

To further clarify exactly what I need to achieve.

The column T4 refers to a temperature - this temperature value can come from one of ten probes. The "mapping" of the probe to temperature column is done via another table. So the column value could be Tx where x is between 1 and 10.

So before this I determine the mapping value

DECLARE @TLink nvarchar(3) SELECT @TLink = TempLinks FROM DCUConfigurations WHERE DCUID = @DCUId

Answer 1

you cannot use table column variable name in query. you should use dynamic queries. ie.

DECLARE @TLink nvarchar(3)
SET @TLink = 'T4'

exec('select '+@TLink+' from myTable')

though i would avoid using these as much as i can. Better rewrite your question what are you trying to achieve so we can help you with achieving it without dynamic queries which are unstable and unreadable imho.

Answer 2

You are casting T4 to a decimal, this isn't possible hence the error message.

I am guessing you want to dynamically pass the name of the column. Before you start I suggest reading this article http://www.sommarskog.se/dynamic_sql.html

Answer 3

You can achieve this using CASE statements, although it could get messy if you have a lot of column options. For example, if you could choose the columns T1, T2, T3 or T4 your SELECT statement would look something like this:

SELECT  @MaxAmbient = MAX(CAST(CASE @Tlink WHEN 'T1' THEN T1 WHEN 'T2' THEN T2 WHEN 'T3' THEN T3 ELSE T4 END AS DECIMAL))/10 
      , @MinAmbient = MIN(CAST(CASE @Tlink WHEN 'T1' THEN T1 WHEN 'T2' THEN T2 WHEN 'T3' THEN T3 ELSE T4 END AS DECIMAL))/10
      , @AverageAmbient = AVG(CAST(CASE @Tlink WHEN 'T1' THEN T1 WHEN 'T2' THEN T2 WHEN 'T3' THEN T3 ELSE T4 END AS DECIMAL))/10