如何在 SQL Server 中声明数组变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41671882/
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
How to declare Array variable in SQL Server?
提问by mohamed faisal
I want to execute one query in Stored Procedure, that should loop all the array values.
我想在存储过程中执行一个查询，它应该循环所有数组值。
For example:
例如：
declare arrayStoreID={1001,2400,2001,5000}
for(int i=0;i<arrayStoreID.length;i++)
{
select
col_name1,col_name2
into
@temp_table
from
Table_Name
Where
storeID=arrayStoreID[i]
}
I want to perform like above. Thanks
我想像上面那样执行。谢谢
回答by Susang
First Store IDs in temporary tableas below
第一个商店 IDtemporary table如下
create table #Table_Name(storeID INT, col_name1 varchar(50), col_name2 varchar(50))
insert into #Table_Name values
(1001, 'Test1', 'Test2'),
(5000, 'Rest1', 'Rest2'),
(1122, 'Best1', 'Best2')
Then you can join with the table from where you want to fetch the record as below,
this method is far better than going through the loopif your requirement is not more complicatedin real
然后你可以从你想要获取记录的地方加入表，如下所示，loop如果你的要求不是more complicated真实的，这种方法比通过这个方法要好得多
select t.col_name1,
t.col_name2
INTO #new_table
from #Table_Name t
inner join #tmp_ids ti on ti.id = t.storeID
It will return that two records which is matched with IDsand inserted into the
#new_tableabove
它将返回与上面匹配IDs并插入的
两条记录#new_table
select * from #new_table
OUTPUT:
col_name1 col_name2
Test1 Test2
Rest1 Rest2
Note: you can use `table variable` as well
回答by P?????
use INclause.
使用IN条款。
You don't need loop or temp table to pass storeID. Pass the list of storeID'sin INclause
您不需要循环或临时表来传递storeID. 传递storeID'sinIN子句的列表
select
col_name1,col_name2
into
#temp_table -- cannot use @table here
from
Table_Name
Where
storeID in (1001,2400,2001,5000)
回答by Joe Taras
Arrayobject is not present in Sql Server.
Array对象在 Sql Server 中不存在。
You can create a temporary table, as follow
可以创建一个临时表，如下
CREATE TABLE #mytemp (<list of field>)
where you can store your information.
您可以在哪里存储您的信息。
You can perform a JOIN operation to use that with other tables or if you want to create a loop you can define a CURSORto process every row of your temporary table
您可以执行 JOIN 操作以将其与其他表一起使用，或者如果您想创建一个循环，您可以定义一个CURSOR来处理临时表的每一行
