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?

No it is not possible , sql does not have arrays and you cannot select from variable. — P.Salmon
– P.Salmon, Commented Jul 27, 2019 at 20:34
SQL Server does not support macro substitution... this leaves dynamic SQL — John Cappelletti
– John Cappelletti, Commented Jul 27, 2019 at 21:02
You can execute sql queries you build yourself...basically build a string, and execute it with sp_executesql — Clay
– Clay, Commented Jul 27, 2019 at 21:03
The problem with dynamic SQL here, is you'd have to pass all your input values as a varchar, which is less than ideal. — Thom A
– Thom A ♦, Commented Jul 27, 2019 at 21:03
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. — Dan Guzman
– Dan Guzman, Commented Jul 27, 2019 at 22:18

sticky bit · Accepted Answer · 2019-07-28 00:57:03Z

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

edited Jul 28, 2019 at 0:57

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

answered Jul 27, 2019 at 22:17

benjamin moskovits

5,5441 gold badge14 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related