Need to assign a Column name through a variable SQL

Question

What I am trying to do here is create a dynamic query where DBAs only have to change the column name at the variable level, the reason of this is because we have multiple DBs that were horrendously created and even though they have the same data, the column names change from one to another (there is no normalization between them).

I am working on a query where it has around 400 lines and what I am trying to avoid is pass the logic to another DBA where they have to go through the script and replace all the column names... but instead just assign the column name that match from their DB to the logic. Not sure if this could be possible.

This is an example:

Declare @ColumnA 
Declare @ColumnB
set @ColumnA = 'UserID'
set @ColumnB = 'Salary'

select @ColumnA,@ColumnB from Table

How can I achieve this kind of functionality?

Answer 1

You can use dynamic SQL like that:

DECLARE @ColumnA nvarchar(max),
        @ColumnB nvarchar(max),
        @table nvarchar(max)
        @sql nvarchar(max)

SELECT  @ColumnA = N'UserID',
        @ColumnB = N'Salary',
        @table = N'Table'

SELECT @sql = N'SELECT ' +QUOTENAME(@ColumnA)+','+QUOTENAME(@ColumnB)+ ' FROM '+QUOTENAME(@table) +';'

EXEC sp_executesql @sql

It is a good practice to use QUOTENAME in such situations, it will save you from injections, or spaces inside column names.

QUOTENAME - returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier. Return nvarchar(258). So UserID became [UserID].

Answer 2

I've made a dynamic query. You can assign column and table names to the variables.

            Declare @ColumnA  Varchar(50)
            Declare @ColumnB Varchar(50)
            Declare @tablename Varchar(50)

            set @ColumnA = 'UserID'
            set @ColumnB = 'Salary'
            set @tablename = '#table'

            declare @Query nvarchar(max)

            set @Query='select '+@ColumnA+','+@ColumnB+'
                    FROM '+@tablename+' (NOLOCK)'

            -- print @Query  -- to see desired query.

            execute SP_executesql  @Query 

Please let us know if you have any concerns.