CODEWITHSUNDEEP
sqlsql-serverstored-procedures
mohamed faisal
mohamed faisal

Reputation: 446

How to declare Array variable in SQL Server?

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

Upvotes: 14

Views: 76900

Answers (6)

Jose Manuel Lepe
Jose Manuel Lepe

Reputation: 121

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)

Upvotes: 1

Van2k
Van2k

Reputation: 31

  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

Upvotes: 1

mostafa toloo
mostafa toloo

Reputation: 61

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

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

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)

Upvotes: 3

Shushil Bohara
Shushil Bohara

Reputation: 5656

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

Upvotes: 15

Joe Taras
Joe Taras

Reputation: 15389

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

Upvotes: 3

Related Questions