Iterate Through Array Of Data In SQL Query

Scenario

You have a set of data, and you want to execute a set of queries on the records in a SQL table which are matching with this set of data.
 
You have got a list of Product IDs from the QA department, you need to get the details of those products from the SQL table. And this situation happens frequently. How would you do this? What are the options you have?
 
Easy solution – You would write select query and put product ID in where clause and get the details. Do this for all the product IDs you have got. It is a time-consuming task.
Is there any better way of achieving it where we can get the result in one query?
 

Solution

 
Yes, we can write a stored procedure, or you can just prepare a set of queries together to run for a single time as well.
 
We will use temporary table variable to create arrays in SQL. We will insert the set of data (what you already have) into a temporary array variable. Use While clause, insert into clauses to generate our result table – which will show details of products matching product IDs.
 
Below is a sample set of queries which generates an array variable named MYARRAY, you can store your data in this array. Then we will iterate through these array values using a WHILE clause, we have used two variables to loop through the array – INDEXVAR and TOTALCOUNT. As usual, the loop will continue until INDEXVAR is smaller than TOTALCOUNT.
 
Using INDEXVAR and WHERE clause, we will get current array index value. We will use this value to fetch data from the actual table and insert it into our temporary result table PRODUCTDETAILSTABLE.
Use DATABASENAME  
GO  
  
DECLARE @PRODUCTDETAILSTABLE table (PRODUCTNAME nvarchar(100), PRODUCTID int, PRODUCTCOST int)  
  
-- Declare your array table variable  
DECLARE @MYARRAY table (TEMPCOL nvarchar(50), ARRAYINDEX int identity(1,1) )  
  
-- Add values to your array table, these are the values which you need to look for in your database  
INSERT INTO @MYARRAY (TEMPCOL)  
   VALUES  
('PRD-2222'), ('PRD-3333'), ('PRD-4563'), ('PRD-4569'), ('PRD-6657'), ('PRD-3452'), ('PRD-6578')  
  
--select * from @MYARRAY  
  
DECLARE @INDEXVAR int  
DECLARE @TOTALCOUNT int  
DECLARE @CURINDEXEDPRODUCTID nvarchar (50)  
SET @INDEXVAR = 0  
SELECT @TOTALCOUNT= COUNT(*) FROM @MYARRAY  
WHILE @INDEXVAR < @TOTALCOUNT  
BEGIN  
    SELECT @INDEXVAR = @INDEXVAR + 1  
  
    -- Get value of current indexed product ID from array table  
    SELECT @CURINDEXEDPRODUCTID = TEMPCOL from @MYARRAY where ARRAYINDEX = @INDEXVAR  
  
    -- Get details of Product matching current indexed product ID from array  
    BEGIN  
        INSERT INTO @PRODUCTDETAILSTABLE (PRODUCTNAME, PRODUCTID, PRODUCTCOST)  
        (  
            select top 1 ProductName as PRODUCTNAME, ProductID as PRODUCTID, Cost as PRODUCTCOST  
            FROM  
            dbo.ProductDetails t  
            where t.ProductID= @CURINDEXEDPRODUCTID  
        )   
    END  
END  
  
Select * from @PRODUCTDETAILSTABLE  

 

You might come across a scenario where you need to update a set of records from your database or delete specific rows from your table – at that time you might need these array queries to fulfill your requirements. That’s it for this article. When someone asks you to pull data for specific list of IDs, then I hope these queries help you to pull out data easily from the database.
 
Thanks for reading!

Similar Articles