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-19T15:21:18.187

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 '15 at 15:21

answered Jul 19 '15 at 15:06

danvasiloiu

751
7
24

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) – kkris77 Jul 19 '15 at 15:20
Yes. Sry i was writing from my phone. I edited. Mark it as a correct answer then. I 'm glad it helped – danvasiloiu Jul 19 '15 at 15:22
I wish I could - I don't have enough reputation, but I am still grateful. – kkris77 Jul 19 '15 at 15:26
1

@kkris77 You don't need a high reputation to close your question! – iDevlop Jul 19 '15 at 15:29

score 1 · Answer 2 · answered Jul 19 '15 at 14:52

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.

Finding column name and using it in select statement

2 Answers2