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?

Hals

This isn't possible without dynamic SQL and I wouldn't recommend transforming a 400 line query into dynamic SQL. Ctrl+H would be a lot easier tbh. – Location

I wonder how you expect 'UserID' to be stored in an int variable. :D – Interflow

haha you are right, I was just trying to make a point, but I only need the query to look the Column name, no the data.. – Hals

@Andrew: No reason why UserId couldn't be an int. – Jermainejerman

@ Mitch, Andrew had a point, I remove the data type, cause I need to hold the Column name, not the data type.. that is what I am trying to find out, if this in fact can be possible – Hals

@MitchWheat, try Declare @ColumnA int = 'UserID' and tell me if you are lucky. ;) – Interflow

Have you thought about creating views to standarize the column names? – Interflow

@Andrew: sorry talking about completely different things! lol – Jermainejerman

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].

Housefly

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.

Pincas

Answer 3

You just need a single variable and pass the required columns as comma separated. Then use the below dynamic script for the expected result.

DECLARE @Columns nvarchar(max),
        @sql nvarchar(max)

SELECT  @Columns = N'UserID,Salary'

SELECT @sql = N'SELECT ' +@Columns+ ' FROM YourTable'

EXEC sp_executesql @sql

Emphatic