CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sql
Juan Carlos Araya Portuguez
Juan Carlos Araya Portuguez

Reputation: 161

Need to assign a Column name through a variable SQL

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?

Upvotes: 8

Views: 23437

Answers (3)

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

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.

Upvotes: 1

gofr1
gofr1

Reputation: 15977

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

Upvotes: 5

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

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

Upvotes: 0

Related Questions