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?

Does your example contain 3 different SQL statements or only 1? Also what SQL are you using? In addition it would help if you could give is near which of those lines the incorrect syntax is near. — jsalonen, Dec 01 '11 at 23:38
I think the question is basically "Does SQL Server support arrays?" and I believe the answer is no, but I'll wait for a SQL Server expert to comment. — Mike Christensen, Dec 01 '11 at 23:43
@MikeChristensen, I am not declaring myself as an expert but no, SQL Server does not support arrays — Filip Popović, Dec 01 '11 at 23:48
If you're asking questions like this, you should specify which database you're using, as syntax and support changes - for instance, SQL Server might accept something that MySQL doesn't (and vice versa). The tag `SQL` is too generic for specific SQL questions. — Ken White, Dec 01 '11 at 23:50
possible duplicate http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql — Filip Popović, Dec 01 '11 at 23:54
@FilipPopović - It's not a duplicate if you don't know what the reference to `split` means in order to search for it. — Ken White, Dec 02 '11 at 00:12
@KenWhite, You are right, it is not duplicate since OP didn't ask for splitting string but using arrays in SQL Server. My bad. — Filip Popović, Dec 02 '11 at 00:17
Your question is misleading. The `Incorrect syntax near ','.` message is generated if you use `SET`, not `SELECT`, to assign the variable. If you use `SELECT`, the message is quite different: `A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.` — Andriy M, Dec 02 '11 at 12:28

score 60 · Answer 1 · answered Nov 21 '13 at 05:17

60

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

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

answered Nov 21 '13 at 05:17

code save

1,054
1
9
15

6

This should probably be the selected answer because it does exactly what the OP seems to be asking for. – Richard Barker Jan 29 '16 at 16:27
1

This `SELECT * FROM @tab` is strange! We don't want to select from the variable we just declared, we want to `select from Accounts` The "in" is deceiving and basically an "=" since FirstName only holds one value per row in `@tab`. The `SELECT FROM Accounts` from lavazza_or_jps's answer shows what's wanted and works fine – Niek Apr 19 '16 at 11:59

Ken White · Accepted Answer · 2018-09-28T22:22:31.020

10

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

edited Sep 28 '18 at 22:22

answered Dec 01 '11 at 23:49

Ken White

123,280
14
225
444

Another approach is to parse into a TEMP table then join on that. – Mike Christensen Dec 02 '11 at 00:02
@Mike: True, but a) that requires parsing first, and b) if the user doesn't know how to assign to a string variable (not counting the `SET` error), the parsing would probably be a little much, and I wouldn't know how to suggest a search for that example. Good point, though. – Ken White Dec 02 '11 at 00:11
What is the difference between SET and SELECT? I thought you could use either. – HL8 Dec 02 '11 at 00:25
@HL8: [SET](http://msdn.microsoft.com/en-us/library/ms189484.aspx) sets the value of a local variable directly, while [SELECT](http://msdn.microsoft.com/en-us/library/ms187330.aspx) requires you to `DECLARE` the variable first separately. They're similar, but not interchangeable. – Ken White Dec 02 '11 at 00:57
@HL8: Re SET vs SELECT: Ken is mistaken. Both SET and SELECT require the variable to be declared, so there's no difference in that. In fact, for the purpose of this particular question the two are most likely interchangeable, though in general, as Ken correctly points out, they each indeed have their own specifics. – Andriy M Dec 02 '11 at 12:35
One big difference between set an select would be the where clause which from my understanding would not work with a set statement but will with a select statement. Please Correct Me If I Am Wrong. – Richard Barker Jan 29 '16 at 16:26
@Richard: No, the where will not work on either of them. See my last paragraph above. – Ken White Jan 29 '16 at 16:38

score 7 · Answer 3 · answered Apr 17 '15 at 15:19

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

score 6 · Answer 4 · answered Jun 28 '21 at 07:09

6

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, ','))

answered Jun 28 '21 at 07:09

Floris Alexandrou

71
1
5

this! and in case STRING_SPLIT is not available for your database version, you can use this custom method; https://stackoverflow.com/a/10914602/18391068 this function worked for me. – Emre Bener Feb 17 '23 at 06:45

score 1 · Answer 5 · answered Apr 07 '20 at 17:27

1

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

answered Apr 07 '20 at 17:27

Arsen Khachaturyan

7,904
4
42
42

score 0 · Answer 6 · answered Sep 09 '22 at 13:43

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.

Set variable value to array of strings

6 Answers6