SQL 将变量值设置为字符串数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8349915/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set variable value to array of strings
提问by HL8
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':
我在行中收到语法错误select @FirstName = 'John','Sarah','George'：
Incorrect syntax near ','
Is there any way I can set the variable with many values?
有什么办法可以设置具有多个值的变量？
采纳答案by Ken White
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.
您正在尝试将三个单独的字符串文字分配给单个字符串变量。一个有效的字符串变量是'John, Sarah, George'. 如果您想在双引号之间嵌入单引号，则必须对它们进行转义。
Also, your actual SELECTwon'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.)
此外，您的实际SELECT将不起作用，因为 SQL 数据库不会将字符串变量解析为单独的文字值。您需要改用动态 SQL，然后执行该动态 SQL 语句。（dynamic SQL使用您正在使用的数据库引擎作为主题（如[sqlserver] dynamic SQL），在此站点中搜索，您应该会得到几个示例。）
回答by code save
declare @tab table(FirstName varchar(100))
insert into @tab values('John'),('Sarah'),('George')
SELECT *
FROM @tab
WHERE 'John' in (FirstName)
回答by lavazza_or_jps
-- 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
回答by Arsen Khachaturyan
In SQLyou can not have a variable array.
However, the best alternative solution is to use a temporary table.
在SQL你不能有一个可变数组。
但是，最好的替代解决方案是使用临时表。
