Thread: Variable Column Name - MS SQL

How do you use a variable as a column name in a MS SQL statement?

Answer is you cant do that directly. You have to build and run the query dynamically. Like this:

Code:

declare @sql nvarchar(2000)
set @sql = 'select ' + @columnName + ' from myTable'
EXEC(@sql)

1. @scheduleID int,
2. @field varchar(50),
3. @value varchar(50)
4. As
5. If (@value = 'ffffff')
6. begin
7. UPDATE ModaSchedule SET
8. @field = '000066'
9. WHERE scheduleID = @scheduleID
10. end
11. else
12. begin
13. UPDATE ModaSchedule SET
14. @field = 'ffffff'
15. WHERE scheduleID = @scheduleID
16. end

i was searching answer this question but you posted ..
thank you..

I think you may have answered my question but am not quite sure. Here is my issue.

I have a table with one record per certificate a user has. So, user SMITH can have multiple records, one for DMV another for CPT and another for ICD. SMITH has three certificates but can have up to 20.

The desire is to have a second table with one record per user with columns for each certificate indicating with Y or N whether the user has that certificate. The column names are the same as the certificate name (eg. DMV, CPT, ICD,...)

So, is there a way to read in the first table, use the data in the certificate field (DMV, CPT, ICD, etc) to the reference the column in the second table and update the respective column to a "Y" to indicate the user has that certificate? For example: if table2:ColumnName(DMV)=table1:Certificate("DMV") then update table2:Column(DMV)="Y"
The input file we use to create table 1 is in the one record per certificate per user design. We do not control that.

The intent is to avoid long Case statements. If this works, we have another similar application with a possible 130 roles a user can have. I've Google this multiple was and read lots of possibilities but none seem to indicate this can be done or I just don't understand it.

Can I do this? If so, how? Your answer above seems to indicate I can but I don't quite understand how. Any help will be much appreciated. Thank you!