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. Its 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 also.
 
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 array variable named MYARRAY, you can store your data in this array. Then we will iterate through these array values using WHILE clause, we have used two variables to loop through 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 actual table and insert it into our temporary result table PRODUCTDETAILSTABLE.
  1. Use DATABASENAME  
  2. GO  
  3.   
  4. DECLARE @PRODUCTDETAILSTABLE table (PRODUCTNAME nvarchar(100), PRODUCTID int, PRODUCTCOST int)  
  5.   
  6. -- Declare your array table variable  
  7. DECLARE @MYARRAY table (TEMPCOL nvarchar(50), ARRAYINDEX int identity(1,1) )  
  8.   
  9. -- Add values to your array table, these are the values which you need to look for in your database  
  10. INSERT INTO @MYARRAY (TEMPCOL)  
  11.    VALUES  
  12. ('PRD-2222'), ('PRD-3333'), ('PRD-4563'), ('PRD-4569'), ('PRD-6657'), ('PRD-3452'), ('PRD-6578')  
  13.   
  14. --select * from @MYARRAY  
  15.   
  16. DECLARE @INDEXVAR int  
  17. DECLARE @TOTALCOUNT int  
  18. DECLARE @CURINDEXEDPRODUCTID nvarchar (50)  
  19. SET @INDEXVAR = 0  
  20. SELECT @TOTALCOUNT= COUNT(*) FROM @MYARRAY  
  21. WHILE @INDEXVAR < @TOTALCOUNT  
  22. BEGIN  
  23.     SELECT @INDEXVAR = @INDEXVAR + 1  
  24.   
  25.     -- Get value of current indexed product ID from array table  
  26.     SELECT @CURINDEXEDPRODUCTID = TEMPCOL from @MYARRAY where ARRAYINDEX = @INDEXVAR  
  27.   
  28.     -- Get details of Product matching current indexed product ID from array  
  29.     BEGIN  
  30.         INSERT INTO @PRODUCTDETAILSTABLE (PRODUCTNAME, PRODUCTID, PRODUCTCOST)  
  31.         (  
  32.             select top 1 ProductName as PRODUCTNAME, ProductID as PRODUCTID, Cost as PRODUCTCOST  
  33.             FROM  
  34.             dbo.ProductDetails t  
  35.             where t.ProductID= @CURINDEXEDPRODUCTID  
  36.         )   
  37.     END  
  38. END  
  39.   
  40. 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!