Get column value from string column name sql

Question

Is this possible to get multiple columns value when we have column name as string Like if i have a table Test and i have columns FirstName , LastName , Address .

Now what i want to get value of all three columns but i want to make this dynamic so that i just pass string column name i get values for that columns

Example

Select 

(select column_name from metadata )

from source table 

Answer 1

Pass the column names as parameters

DECLARE @COLS NVARCHAR(MAX)
DECLARE @TABLE NVARCHAR(MAX)
SET @COLS = 'COL1,COL2'
SET @TABLE = 'TABLENAME'

Now execute the query

DECLARE @QRY NVARCHAR(MAX)
SET @QRY = 'SELECT (SELECT '+@COLS+' FROM '+@TABLE+') FROM sourcetable'
EXEC SP_EXECUTESQL @QRY

Answer 2

You can build the query in code dynamically. However it needs to be robust so that it does not gets prone to SQL injection. Something like this:

string commandString = "select {0} from SomeTable";
SqlCommand command = new SqlCommand();
command.CommandText = string.Format(commandString, "selected column names");
command.EndExecuteReader();

In SQL:

declare @query nvarchar(500)

set @query  =  replace('select 0 from author','0','column names from some parameter') 

execute sp_executesql @query 

Update 2: Does this do what you need?

declare @query nvarchar(500)
DECLARE @columnNames varchar(1000)

set @columnNames = ''
SELECT @columnNames = @columnNames + column_name  + ',' FROM metadata
set @query  =  replace('select 0 from source_table','0',SUBSTRING(@columnNames,0,LEN(@columnNames)-1)) 

execute sp_executesql @query