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

Surmount

SQL Server does not support arrays – Victorvictoria

I think instead of a loop, you can use a simple 'IN' here, '... FROM Table_Name WHERE storeID IN (1001,2400,2001,5000)' – Nonrigid

@a_horse_with_no_name Is there any other way? – Surmount

@AbdulRasheed No, My actual scenario is not like that. – Surmount

Use a cursor and a temp-table or table variable? – Katherinakatherine

Possible duplicate of How to declare array inside Sql Server Stored Procedure? – Ackerman

can you add more details? with expected output and requirement – Foursquare

There is no such a thing as an array in SQL Server. – Ningpo

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

Resolution

Answer 2

Array object is not present in 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 CURSOR to process every row of your temporary table

Brandon

No brother. My scenario is different. link. What about this link? – Surmount

@mohamedfaisal: Dear, in your link, an array creation as emulated with temp table (Solutiion 1). If you add in your question your aim, I can help you to write a solution – Brandon

Answer 3

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)

Peduncle

Answer 4

1. No, there is no ARRAY variable in SQL Server
2. No, there is no FOR Loop in SQL Server
3. It is only CURSOR and While loop

Fodder

Answer 5

you can use the cursor attribute

declare @col_name1 type,@col_name2 type
declare crsr cursor for select  col_name1,col_name2 from Table_Name Where storeID in (1001,2400,2001,5000)
open crsr
fetch next from crsr into @col_name1,@col_name2 
while @@fetch_status=0
begin
    insert into @temp_table(col_name1,col_name2) values (@col_name1,@col_name2)
end
close crsr
deallocate crsr

Arvad

Answer 6

As others said in this post, there's no array type because of the natural behavior of a DB and because of that there's no for or foreach, but there are tables and they could work kinda the same.

First you'll declare a variable as a table:

DECLARE @TempTable TABLE ([Id] INT)

Then you'll insert the respective Ids as you want

INSERT INTO @TempTable VALUES (1001), (2400), (2001), (5000)

And at the end you can use that table the way you prefer more

SELECT
    col_name1
    , col_name2
FROM Table_Name
WHERE storeID IN (SELECT [Id] FROM @TempTable)

Scraper