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
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
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
