1

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?

Lumpi
  • 2,697
  • 5
  • 38
  • 47
  • 1
    Yes, but 'tmin','tmax' and 'MatCode' are not part of the table `information_schema.columns` – Widor Oct 07 '11 at 13:21

2 Answers2

1

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
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
1

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.

StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • Ahh, Sorry, I forgot the most imortant part: As this is part of a UDF I don't want a hardcoded Ecomp in the select statment, but a variable like @returncolumn. When I try that I only get the requestet column name back, not the value stored. – Lumpi Oct 07 '11 at 13:39
  • "But it's all pretty horrible" ;-) I see that. But thank you for the very nice "how to". Thanks! – Lumpi Oct 07 '11 at 14:09
  • Glad you found this useful. Just don't tell my boss that I wrote SQL like this ;) – StuartLC Oct 07 '11 at 14:18
  • This looks prone to SQL injection attacks—it's an example of how stored proecures are not necessarily protection against them. – D'Arcy Rittich Oct 07 '11 at 14:57
  • @RedFilter - have updated with parameterized sp_executesql. Well spotted. – StuartLC Oct 07 '11 at 15:11