CODEWITHSUNDEEP
sqlsql-server-2008
HL8
HL8

Reputation: 1419

Set variable value to array of strings

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?

Upvotes: 27

Views: 187980

Answers (6)

Zahari Kitanov
Zahari Kitanov

Reputation: 578

I just want to extend @Code Save's answer

--collection table is required, since we cannot use directly arrays in TSQL
declare  @CollectionTable table(FirstName  varchar(100))
insert into @CollectionTable   values('John'),('Sarah'),('George')

SELECT * FROM TargetTable
WHERE Name IN (SELECT * FROM @CollectionTable)

In this way we can use the result from the SELECT statement from our @CollectionTable to be evaluated in the IN operator. And of course we can re-use the @CollectionTable as many times as we need.

Upvotes: 1

Floris Alexandrou
Floris Alexandrou

Reputation: 95

A quick way to turn your varchar variable to a table (array of values) is to have your FirstName variable as a whole varchar first and then use the STRING_SPLIT method.

declare @FirstName varchar(100)
select @FirstName = 'John,Sarah,George'

SELECT * 
FROM Accounts
WHERE FirstName in (SELECT * FROM STRING_SPLIT(@FirstName, ','))

Upvotes: 8

Arsen Khachaturyan
Arsen Khachaturyan

Reputation: 8330

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

Upvotes: 1

Ken White
Ken White

Reputation: 125708

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

Upvotes: 10

lavazza_or_jps
lavazza_or_jps

Reputation: 71

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

Upvotes: 7

code save
code save

Reputation: 1106

declare  @tab table(FirstName  varchar(100))
insert into @tab   values('John'),('Sarah'),('George')

SELECT * 
FROM @tab
WHERE 'John' in (FirstName)

Upvotes: 65

Related Questions