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