7

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
Ancient
  • 3,007
  • 14
  • 55
  • 104

2 Answers2

8

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
Sarath Subramanian
  • 20,027
  • 11
  • 82
  • 86
1

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
danish
  • 5,550
  • 2
  • 25
  • 28