Can a SELECT statement use variables containing field names?

Question

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.

You would need to dynamically build a SQL query string using your variables, then execute that query string directly. — Tim Biegeleisen, May 06 '21 at 03:04
Please do read [The Curse and Blessings of Dynamic SQL](https://www.sommarskog.se/dynamic_sql.html) — Squirrel, May 06 '21 at 03:07
Thanks, @TimBiegeleisen. I'm looking at how to do that properly now. — MJA, May 06 '21 at 03:07
Thanks, @Squirrel. Great info! It'll take me some time to wrap my head around it. — MJA, May 06 '21 at 04:04
TL;DR; always use `QUOTENAME` on injected object/column names, verify names against `sys.objects`/`sys.columns`, always use `nvarchar` not `varchar`, always pass through parameters of actual data via `sp_executesql` where possible, never aggregate by coalescing a variable (use `STRING_AGG` or `FOR XML`) — Charlieface, May 06 '21 at 04:23
It is often the case that when I understand the actual reason for using dynamic SQL there is a better way to solve the problem which does not include dynamic SQL -- what are you actually trying to do? — Hogan, May 06 '21 at 12:08

score 1 · Accepted Answer · answered May 06 '21 at 12:02

1

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

answered May 06 '21 at 12:02

prem

223
3
13

Thanks, @premprasath. I was trying to avoid using the EXEC after reading "The Curse and Blessings of Dynamic SQL" but I don't see any other way to create a dynamic statement where the SELECT clause includes variables for field names. – MJA May 07 '21 at 07:17

Can a SELECT statement use variables containing field names?

1 Answers1