Use variable Column name in Select statement on SQL server 2008

I would like to select a value from a table using the Column name AS A VARIABLE !

eg

DECLARE @spalte as varchar(10) 
SET @spalte = 'Ecomp'

SELECT @spalte FROM dbo.MATDATA WHERE  2>= tmin AND 2<=tmax AND 1 = MatCode

When I try to do this I on开发者_JAVA百科ly get 'Ecomp' back, not the expected value.

Any idea?

information_schema is meta data describing the objects in the database - it isn't a placeholder your table.

If you just want to return data from your table, then

SELECT * FROM dbo.MATDATA WHERE 2 >= tmin AND 2<=tmax AND 1 = MatCode

If you want to build up a query against a table that you don't have the schema for, then you will need to build up dynamic SQL query and then call sp_executesql.

Edit :

To Select just the one column:

SELECT EOComp FROM dbo.MATDATA WHERE 2 >= tmin AND 2<=tmax AND 1 = MatCode

Edit #2 :

Your updated question doesn't bear much resemblance to the original Q, and you've already accepted Redfilter's answer.

To select a dynamic column, you would need dynamic SQL. But you can't call procs from a UDF, and UDFs should return a standard type (if its scalar) or table.

Here's how to do this from a SPROC:

Given

create table dbo.MATDATA
(
    Column1 INT,
    Column2 NVARCHAR(100),
    Column3 DateTime,
    tmin int,
    tmax int,
    MatCode int
)

And PROC

create proc dbo.GetColumnFromMATDATA(@spalte nvarchar(256))
as
    begin
        set nocount on
        DECLARE @SQL NVARCHAR(MAX)
        SET @SQL = 'SELECT @spalte FROM dbo.MATDATA WHERE 2>= tmin AND 2<=tmax AND 1 = MatCode'
        exec sp_executesql @SQL, N'@spalte nvarchar(256)', @spalte=@spalte
    end

You can then do

exec dbo.GetColumnFromMATDATA 'Column1'
exec dbo.GetColumnFromMATDATA 'Column2'
exec dbo.GetColumnFromMATDATA 'Column3'
exec dbo.GetColumnFromMATDATA 'tmax'

etc

But it's all pretty horrible, IMHO.

You are querying table information_schema.columns, not table MATDATA. Additionally, you are trying to query column data. information_schema.columns only contains metadata about the columns, not the data contained by those columns.

Instead, it looks like you may want:

SELECT *
FROM dbo.MATDATA
WHERE 2 >= tmin
    AND 2 <= tmax
    AND 1 = MatCode