Use Variable as SQL column Name in query

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  February 12, 2010 at 9:56 am

  #229945

  Hi all,

  Is it poosible to use a variable as a column name in SQL?

  EXAMPLE:

  DECLARE @ColumnName VARCHAR(100)

  set @ColumnName= 'Date Received '+ GETDATE()

  SELECT Datecolumn as @ColumnName

  FROM TABLE1

  Quiet obviously I am getting an error here...

  I found a way to do it but that by keeping the select query in a diffrent variable like this:

  SET @sqlquery = N'SELECT DISTINCT (' + QUOTENAME(@COLUMNNAME) + ') FROM TABLE1'

  but It doesn't satisfy my need, as I wanna use it in my select query directly...

  I'm not sure if its possible but my googling so far ........

  thanks for ur help

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• Steve Cullen

  SSCertifiable

  Points: 5598

  More actions

  February 12, 2010 at 10:10 am

  #1118142

  I'm not really sure what your requirement is. You will need to use some form of dynamic sql.

  declare @ColumnName varchar(50)

  declare @sql nvarchar(max)

  set @ColumnName = 'SalesData_' + convert(varchar(2),datepart(dd,getdate()))

  set @sql = 'select ' + @ColumnName + ' from SalesTable'

  print @sql

  EXEC sp_sqlexec @sql

  Converting oxygen into carbon dioxide, since 1955.

• chaitukadivella

  SSC Enthusiast

  Points: 119

  More actions

  August 7, 2012 at 12:06 pm

  #1522766

  This works fine when the table is in default schema that is dbo. Can you specify how we can make this query work when the table is in some other schema.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 7, 2012 at 12:11 pm

  #1522770

  chaitukadivella (8/7/2012)

  ---

  This works fine when the table is in default schema that is dbo. Can you specify how we can make this query work when the table is in some other schema.

  Is the other schema known and static or can it change as well?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 7, 2012 at 12:38 pm

  #1522779

  chaitukadivella (8/7/2012)

  ---

  This works fine when the table is in default schema that is dbo. Can you specify how we can make this query work when the table is in some other schema.

  The simple answer is this:

  declare @ColumnName varchar(50)

  declare @sql nvarchar(max)

  set @ColumnName = 'SalesData_' + convert(varchar(2),datepart(dd,getdate()))

  set @sql = 'select ' + @ColumnName + ' from yourschema.SalesTable'

  print @sql