Is it possible to use an array to represent columns in SQL Server

Question

I am trying to create a query where the column are variable

Exp

Declare @parm1 int ;
Declare @param2 varchar(50) ;
Declare @param3 int ;
Select Id, name, age from @table_variable
Where
Id=@param1
Name= @param2
Age=@param3

instead of manually typing the columns I want to select each time, I want to replace "I'd, name, age" With an array

@column_array =[list of columns I want to select ] 

The list of columns is decided automatically from a winform app with check box list in visual studio that is variable in length, and values Is that even possible?

• P.Salmon: No it is not possible , sql does not have arrays and you cannot select from variable.
• John Cappelletti: SQL Server does not support macro substitution... this leaves dynamic SQL
• Clay: You can execute sql queries you build yourself...basically build a string, and execute it with sp_executesql
• Thom A: The problem with dynamic SQL here, is you'd have to pass all your input values as a varchar, which is less than ideal.
• Dan Guzman: Simply build the SQL statement in the application code using the list of selected columns and execute the query. No need to use T-SQL for the task.

Answer 1

No you cannot use a variable in that way. You can however use a variable to build the statement dynamically and execute the statement like so: The code assumes that @col1, @col2 and @table_variable have been declared and assigned values.

Declare @tsql varchar(2500);
Set @tsql = ‘ select ‘;
Set @tsql += @col1 +’, ‘;
Set @tsql += @col2;
Set @tsql ‘ from @table_variable;’
Print @tsql;
Exec (@tsqll);