SELECT into variable, using another variable as the column name

Question

I need to select into a variable using a column name that is also stored in a variable, but it just selects the value of the column name instead.

The code below loops through all of the columns for a table and finds the old and new value of each column (part of a TRIGGER). The problem is, instead of assigning the old and new value to the variables, it assigns the value of the @fieldname variable.

Set @getColumnName = Cursor For
    Select Column_name From Information_Schema.Columns Where Table_Name = @tablename

Open @getColumnName
    Fetch Next From @getColumnName Into @fieldname 

    While @@FETCH_STATUS = 0
    Begin
        Select @oldValue = @fieldname From deleted
        Select @newValue = @fieldname From inserted

        -- Do something with the values here
        -- @oldValue and @newValue are now equal to @fieldname
        -- Actually want them to be the result of the query
    End
Close @getColumnName

You can't use variables for column names. You can use dynamic SQL but it's a little tricky when setting variables — D Stanley
– D Stanley, Commented Jun 8, 2016 at 14:12
read about COLUMNS_UPDATED in triggers. msdn.microsoft.com/en-us/library/ms186329.aspx — Alex Kudryashev
– Alex Kudryashev, Commented Jun 8, 2016 at 15:45

ryansin · Accepted Answer · 2016-06-09 08:56:04Z

0

Managed to sort this using Dynamic SQL thanks to D Stanley's suggestion

Select * Into #MyInserted From inserted
Select * Into #MyDeleted From deleted

-- New value
Set @SQLString = N'Select @outVar = ' + @fieldname + ' From #MyInserted'
Set @ParmDefinition = N'@outVar varchar(max) Output'

Execute sp_executesql 
    @SQLString, 
    @ParmDefinition, 
    @outVar = @newValue Output;

-- Old value
Set @SQLString = N'Select @outVar = ' + @fieldname + ' From #MyDeleted'

Execute sp_executesql 
    @SQLString, 
    @ParmDefinition, 
    @outVar = @oldValue Output;

answered Jun 9, 2016 at 8:56

ryansin

1,8573 gold badges28 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

SELECT into variable, using another variable as the column name

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related