Set variable value to array of strings

Question

I want to set a variable as a string of values  E g   declare  FirstName char 100  select  FirstName    John   Sarah   George   SELECT    FROM Accounts WHERE FirstName in   FirstName    I m getting a syntax error in the line select  FirstName    John   Sarah   George    Incorrect syntax near       Is there any way I can set the variable with many values

User · Answer

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

User · Answer

You re trying to assign three separate string literals to a single string variable  A valid string variable would be  John  Sarah  George   If you want embedded single quotes between the double quotes  you have to escape them   Also  your actual SELECT won t work  because SQL databases won t parse the string variable out into individual literal values  You need to use dynamic SQL instead  and then execute that dynamic SQL statement   Search this site for dynamic SQL  with the database engine you re using as the topic  as in  sqlserver  dynamic SQL   and you should get several examples

User · Answer

In SQL you can not have a variable array  However  the best alternative solution is to use a temporary table

User · Answer

declare   tab table FirstName  varchar 100   insert into  tab   values  John     Sarah     George    SELECT    FROM  tab WHERE  John  in  FirstName

[sql] Set variable value to array of strings

Examples related to sql

Examples related to sql-server-2008