Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Use Variable as SQL column Name in query Expand / Collapse
Author
Message
Posted Friday, February 12, 2010 9:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 30, 2014 1:23 PM
Points: 336, Visits: 866
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



---------------------------------------------------

Thanks
Post #864747
Posted Friday, February 12, 2010 10:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
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.

Post #864763
Posted Tuesday, August 7, 2012 12:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:44 AM
Points: 16, Visits: 40
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.
Post #1341459
Posted Tuesday, August 7, 2012 12:11 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
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

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1341464
Posted Tuesday, August 7, 2012 12:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 23,015, Visits: 31,536
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1341475
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse