如何在 SQL Server 中声明数组变量? [英] How to declare Array variable in SQL Server?

问题描述

我想在存储过程中执行一个查询，它应该循环所有数组值.

I want to execute one query in Stored Procedure, that should loop all the array values.

例如:

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

推荐答案

临时表中的First Store IDs如下

First Store IDs in temporary table as below

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

然后你可以加入你想要获取记录的表，如下所示，如果您的需求不是更复杂，那么这种方法比通过loop要好得多

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 loop if your requirement is not more complicated in real

select t.col_name1,
    t.col_name2
INTO #new_table
from #Table_Name t
inner join #tmp_ids ti on ti.id = t.storeID

它将返回与 IDs 匹配并插入到#new_table 以上

It will return that two records which is matched with IDs and inserted into the #new_table above

select * from #new_table

OUTPUT:
col_name1   col_name2
Test1       Test2
Rest1       Rest2

Note: you can use `table variable` as well

这篇关于如何在 SQL Server 中声明数组变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！