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 only 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
information_schema.columns