Using variables in SELECT query

Question

I want to run the following SELECT query:

DECLARE @ColumnName nvarchar(50)
SET @ColumnName = 'AlarmID' -- actually these are calculated 
                            -- by another SELECT but this isn't relevant
SELECT MIN(@ColumnName) FROM INSERTED

This doesn't work, it returns the value of @ColumnName instead of the actual data. How can I make this work?

I cannot put the SELECT into a string and run it with sp_executesql because I will lose access to the INSERTED table (this is running in a trigger).

what is the value returned by `@ColumnName` and your expected result? — John Woo, Feb 25 '13 at 13:49
In the above example, the value the select returns is 'AlarmID', I was expecting an int as the AlarmID column in the INSERTED table is an int. — Tim, Feb 25 '13 at 13:51
maybe you can add sample records with desired result and the *unrelevant* sql query. — John Woo, Feb 25 '13 at 13:51
Have you tried this: http://stackoverflow.com/questions/10092869/can-i-pass-column-name-as-input-parameter-in-sql-stored-procedure — Seth Moore, Feb 25 '13 at 13:55
Also see this post: http://stackoverflow.com/questions/2942082/how-to-pass-column-name-with-parameter-in-insert-sql-statment — Seth Moore, Feb 25 '13 at 13:56

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

EXEC('SELECT MIN(' + @ColumnName + ') FROM INSERTED')

Derived from the link smoore provided.

edited May 23 '17 at 11:43

Community

1
1

answered Feb 25 '13 at 13:57

Bernhard Barker

54,589
14
104
138

score 0 · Answer 2 · answered Feb 25 '13 at 13:54

0

Use this if you want the minimum as a variable:

SELECT @columnName = MIN(@ColumnName) FROM YourTable

answered Feb 25 '13 at 13:54

Antonio Papa

1,596
3
20
38

score 0 · Answer 3 · answered Feb 25 '13 at 13:56

You can't really do that. Your best bet, depending on number of possible values of @ColumnName, is to dynamically set the field value with a case statement, or selectively run the right query using an IF statement:

SELECT CASE @ColumnName WHEN 'AlarmID' THEN MIN(AlarmID) WHEN 'AnotherField' THEN
MIN(AnotherField) END AS MinimumValue FROM INSERTED

OR

IF @ColumnName = 'AlarmID'
  SELECT MIN(AlarmID) FROM INSERTED
ELSE
  ....

Using variables in SELECT query

3 Answers3