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?

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. — ZLK
– ZLK, Commented Oct 5, 2016 at 4:02
I wonder how you expect 'UserID' to be stored in an int variable. :D — Andrew
– Andrew, Commented Oct 5, 2016 at 4:03
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.. — Juan Carlos Araya Portuguez
– Juan Carlos Araya Portuguez, Commented Oct 5, 2016 at 4:06
@ 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 — Juan Carlos Araya Portuguez
– Juan Carlos Araya Portuguez, Commented Oct 5, 2016 at 4:09

gofr1 · Accepted Answer · 2016-10-05 05:38:25Z

5

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

edited Oct 5, 2016 at 5:38

answered Oct 5, 2016 at 4:21

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thom A Dec 20, 2024 at 15:27

As an extra note, the correct data type for an object is sysname, or nvarchar(128). QUOTENAME here will truncate the value, as it expects a sysname, but you should still define the parameters as the correct data type. There's no need for a variable able to hold up to ~1Billion (2GB of) characters.

Frost10000 · Accepted Answer · 2019-08-16 21:59:50Z

1

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.

edited Aug 16, 2019 at 21:59

Frost10000

219 bronze badges

answered Oct 5, 2016 at 5:18

Mr. Bhosale

3,1161 gold badge19 silver badges37 bronze badges

Comments

Unnikrishnan R · Accepted Answer · 2016-10-05 04:55:40Z

0

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

answered Oct 5, 2016 at 4:55

Unnikrishnan R

5,0511 gold badge15 silver badges21 bronze badges

Collectives™ on Stack Overflow

Need to assign a Column name through a variable SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related