[SOLVED] Can a SELECT statement use variables containing field names?

Can a SELECT statement use variables containing field names?

I'm using SQL Server 2008R2 with SSMS17 and am trying to automate some 'least used fields' reports based on current data. Ideally, I'd like to retrieve the list of these fields into a table variable, then create a SELECT statement where I pull the record ID along with only the values of these least used fields, but the variables resolve as text and not a field name. Is what I'm trying possible?

Here's a simplified example using known field names:

    DECLARE @f1 as nvarchar(20) = 'MySpaceID';
    DECLARE @f2 as nvarchar(20) = 'FaxNum';
    DECLARE @f3 as nvarchar(20) = 'PagerNum';
    
    SELECT [ID], [FullName], @f1 as Field1, @f2 as Field2, @f3 as Field3
    FROM tblContacts
    WHERE @f1 is not null
       OR @f2 is not null
       OR @f3 is not null;

What I get instead is the variable value as the output.

    f1          f2      f3
    ----------  ------  ---------
    MySpaceID   FaxNum  PagerNum
    MySpaceID   FaxNum  PagerNum
    MySpaceID   FaxNum  PagerNum

Is this doable?

Thanks.

Solution

EXEC('SELECT [ID], [FullName], ' + @f1 + ' as Field1, ' + @f2 + ' as Field2,' + @f3 + ' as Field3
      FROM tblContacts
      WHERE ' +  @f1 + ' IS NOT NULL
         OR ' +  @f2 + ' IS NOT NULL
         OR ' +  @f3 + ' IS NOT NULL')