Finding column name and using it in select statement

Question

I'm using SQL Server 2012. I would like to write a query where first I look for a column name in specific table using LIKE "%MyName" phrase and then use this column name in select statement to find a value in that column in the table Prąd.

I've tried to do so, using tips from this topic. The code I wrote so far looks like this:

SELECT 
    (
    SELECT c.name AS ColName
    FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    WHERE c.name LIKE 'energia%'
    )   
FROM Prąd
WHERE ID = 
    (
    SELECT MAX(ID) 
    FROM 
    Prąd
    )

What I get as a result is just the column name, not a value in specified column and row in the table Prąd.

danvasiloiu · Accepted Answer · 2015-07-19 15:21:18Z

2

You will need a dynamic statement.

Declare @sqlCommand varchar(max) 
declare @columnName varchar(250)

Set @columnName = ( SELECT c.name  FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE 'energia%' )

Set @sqlCommand = ' select ''' +@columnName + ''' FROM Prąd WHERE ID = ( SELECT MAX(ID) FROM Prąd ) '

Exec @sqlCommand

edited Jul 19, 2015 at 15:21

answered Jul 19, 2015 at 15:06

danvasiloiu

7517 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kkris77 Over a year ago

Thank you. Almost worked perfectly - I got a "name is not a valid identifier" error and it seems that: "If you do not have the brackets, SQL Server assumes the value of the variable to be a stored procedure name" so it should be Exec (@sqlCommand)

danvasiloiu Over a year ago

Yes. Sry i was writing from my phone. I edited. Mark it as a correct answer then. I 'm glad it helped

kkris77 Over a year ago

I wish I could - I don't have enough reputation, but I am still grateful.

Patrick Honorez Over a year ago

@kkris77 You don't need a high reputation to close your question!

underscore_d · Accepted Answer · 2015-07-19 14:52:23Z

1

You need to compose a dynamic SQL statement concatenating the found column name into a string variable, then execute that. Quick searching will show you a couple of available ways to do this. If deploying an application for others to use, ensure you choose a method that is protected against injection.

Also note that if your column name select can possibly return more than one result - which a like filter very much can - you need to account for this in your dynamic SQL composition.

answered Jul 19, 2015 at 14:52

underscore_d

6,8734 gold badges46 silver badges75 bronze badges

Collectives™ on Stack Overflow

Finding column name and using it in select statement

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related