Set variable value to array of strings

Question

I want to set a variable as a string of values. For instance.

declare @FirstName char(100) select @FirstName = 'John','Sarah','George' SELECT * FROM Accounts WHERE FirstName in (@FirstName) 

I get a syntax error in the line select @FirstName = 'John','Sarah','George' :

 Incorrect syntax near ',' 

Is it possible to set a variable with many values?

Answer 1

You are assigning a variable incorrectly. :)

You must use SET , not SELECT .

Secondly, you are trying to assign three separate string literals to one string variable. The actual string variable will be 'John, Sarah, George' . If you need inline single quotes between double quotes, you need to avoid them.

Also, your actual SELECT will not work, as SQL databases will not parse the string variable into separate literal values. Instead, you need to use dynamic SQL, and then execute this dynamic SQL statement. (Find this site for dynamic SQL using the database engine that you use as the theme (as in [sqlserver] dynamic SQL ), and you should get some examples.)

Answer 2

 declare @tab table(FirstName varchar(100)) insert into @tab values('John'),('Sarah'),('George') SELECT * FROM @tab WHERE 'John' in (FirstName) 

Answer 3

 -- create test table "Accounts" create table Accounts ( c_ID int primary key ,first_name varchar(100) ,last_name varchar(100) ,city varchar(100) ); insert into Accounts values (101, 'Sebastian', 'Volk', 'Frankfurt' ); insert into Accounts values (102, 'Beate', 'Mueller', 'Hamburg' ); insert into Accounts values (103, 'John', 'Walker', 'Washington' ); insert into Accounts values (104, 'Britney', 'Sears', 'Holywood' ); insert into Accounts values (105, 'Sarah', 'Schmidt', 'Mainz' ); insert into Accounts values (106, 'George', 'Lewis', 'New Jersey' ); insert into Accounts values (107, 'Jian-xin', 'Wang', 'Peking' ); insert into Accounts values (108, 'Katrina', 'Khan', 'Bolywood' ); -- declare table variable declare @tb_FirstName table(name varchar(100)); insert into @tb_FirstName values ('John'), ('Sarah'), ('George'); SELECT * FROM Accounts WHERE first_name in (select name from @tb_FirstName); SELECT * FROM Accounts WHERE first_name not in (select name from @tb_FirstName); go drop table Accounts; go