CODEWITHSUNDEEP
sql-server
Tim
Tim

Reputation: 337

Using variables in SELECT query

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

Upvotes: 0

Views: 273

Answers (3)

Bernhard Barker
Bernhard Barker

Reputation: 55589

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

Derived from the link smoore provided.

Upvotes: 1

cf_en
cf_en

Reputation: 1661

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

Upvotes: 0

Antonio Papa
Antonio Papa

Reputation: 1666

Use this if you want the minimum as a variable:

SELECT @columnName = MIN(@ColumnName) FROM YourTable

Upvotes: 0

Related Questions