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
– John Woo, Commented Feb 25, 2013 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
– Tim, Commented Feb 25, 2013 at 13:51
maybe you can add sample records with desired result and the unrelevant sql query. — John Woo
– John Woo, Commented Feb 25, 2013 at 13:51
Have you tried this: stackoverflow.com/questions/10092869/… — Seth Moore
– Seth Moore, Commented Feb 25, 2013 at 13:55

Community · Accepted Answer · 2017-05-23 11:43:21Z

1

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

Derived from the link smoore provided.

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Feb 25, 2013 at 13:57

Bernhard Barker

55.7k14 gold badges111 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Antonio Papa · Accepted Answer · 2013-02-25 13:54:34Z

0

Use this if you want the minimum as a variable:

SELECT @columnName = MIN(@ColumnName) FROM YourTable

answered Feb 25, 2013 at 13:54

Antonio Papa

1,6663 gold badges23 silver badges38 bronze badges

Comments

cf_en · Accepted Answer · 2013-02-25 13:56:51Z

0

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

answered Feb 25, 2013 at 13:56

cf_en

1,6611 gold badge10 silver badges18 bronze badges

Collectives™ on Stack Overflow

Using variables in SELECT query

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related