I am trying to retract a column from a table by knowing the header name of the column that I want to retrieve.
select @sName
from VPDW.dbo.Fact_Email_MW100
where VesselID = 3763
and (cast(UTClogTime as date) >= cast(DateAdd(day, -1, GETUTCDATE()) as date))
where
Declare @sName nvarchar(max);
holds the string name of the column that I want to extract from my table.
The whole query needs to be dynamic in order to achieve what you're trying to do. That is, you have to build the SQL and then execute it. Consider something like the following (not tested):
declare @sName nvarchar(max)
declare @sql nvarchar(max)
set @sName = 'some_column'
set @sql = 'select ' + @sName + ' from VPDW.dbo.Fact_Email_MW100 where VesselID = 3763 and (cast(UTClogTime as date) >= cast(DateAdd(day,-1,GETUTCDATE()) as date))'
print @sql
exec sp_sqlexec @sql
sys.columns view for the target table. The best practice when assembling object names into dynamic SQL statements is to use QuoteName() to avoid problems with odd names, e.g. New Table with a space or reserved words like From.
Sometimes one cannot use dynamic SQL (in VIEWs, iTVFs). In such cases you can uses XML's abilities to deal with generic queries:
DECLARE @ColName VARCHAR(100)='name';
SELECT
(
SELECT TOP 1 *
FROM sys.objects
FOR XML PATH('row'),TYPE
).value('(/row/*[local-name()=sql:variable("@ColName")]/text())[1]','nvarchar(max)') AS ValueAtColumnName;
This will get one row of sys.objects and return the value for the column name.
For your query this would look like
Declare @sName nvarchar(max);
SELECT
(
select *
from VPDW.dbo.Fact_Email_MW100
where VesselID = 3763
and (cast(UTClogTime as date) >= cast(DateAdd(day, -1, GETUTCDATE()) as date))
).value('(/row/*[local-name()=sql:variable("@sName")]/text())[1]','nvarchar(max)') AS ValueAtColumnName;