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!