How to declare Array variable in SQL Server?

Question

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

Answer 1

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

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

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

Answer 2

use IN clause.

You don't need loop or temp table to pass storeID. Pass the list of storeID's in IN clause

 select 
        col_name1,col_name2
    into
        #temp_table -- cannot use @table here
    from
        Table_Name
    Where 
        storeID in (1001,2400,2001,5000)