Replacing a Cursor with a single Query possible?

Question

I have this challenge where I'm trying to figure out if I can write something in a single query rather than using a cursor.

The problem is I need to get items from a batch table based on a list of specified suppliers. Each supplier in this list has a weighting and this determines how many items out of the specified "TotalBatchSize" will be fetched per supplier.

My current solution is:

• I pass a "Supplier" list to a Stored Procedure
• Obtain Supplier weighting (say 50% for supplier1 for example) and apply this against the TotalBatchSize to Calculate a "SupplierItemCount"
• I then run a cursor over the "Supplier" list and on each iteration have a query that does a SELECT TOP(@SupplierItemCount) from the batch table and inserts those selected items into a temporary table
• I then return the temporary table after all the suppliers are been iterated over.

Does anyone know a way I can achieve this in a single query or a more efficient approach?

Very much welcome your thoughts!

CREATE PROCEDURE [Queue].[Pull]
@QueueName varchar(100),
@MaxNumberOfItems int = 1 -- number of packets to pick
,@SupplierIds [Configuration].SupplierIds READONLY
AS
BEGIN
BEGIN TRY
BEGIN TRAN
 CREATE TABLE #Suppliers(id int,Name varchar(20), [Priority] int,multiplier float,ItemsToFetch float); 
 CREATE TABLE #Batch(BatchId int, QueueId int,BatchValue varchar(200),SupplierId int);
 -- Confirm table passed through is not empty and has more then one supplier
 IF(EXISTS (SELECT 1 FROM @SupplierIds))
 BEGIN
 print 'Collection of Ids was Passed';
 -- fetch supplier details for parsed SupplierIds
 INSERT INTO #Suppliers (id,Name,[Priority])
 select id,Name,[Priority] from Configuration.Supplier where Id in (select Id from @SupplierIds) and IsActive=1
 END
 ELSE
 BEGIN
 INSERT INTO #Suppliers (id,Name,[Priority])
 select id,Name,[Priority] from Configuration.Supplier where IsActive=1
 END
 -- add all the priority numbers together to determine the packets per supplier to fetch----------------------
 DECLARE @TotalPriority int,@TotalSuppliers int;
 select @TotalPriority=SUM([Priority]),@TotalSuppliers=count(1) from #Suppliers; 
 --select @TotalPriority as TotalPriority,@TotalSuppliers as TotalSuppliers;
 UPDATE #Suppliers 
 SET 
 multiplier=(SELECT CAST([Priority] AS float) / CAST(@TotalPriority AS float))
 ,ItemsToFetch=(SELECT ROUND((SELECT CAST([Priority] AS float) / CAST(@TotalPriority AS float))*@MaxNumberOfItems,0))
 -- due to rounding if 0 then fetch at least one item for supplier
 UPDATE #Suppliers SET ItemsToFetch=(case when ItemsToFetch=0 then 1 else ItemsToFetch end)
 -- LOOP Suppliers,fetch unqiue batch items per supplier---------------------------------------------
 Declare @Id int, @ItemsToFetch int
 DECLARE Supplier_Cursor CURSOR 
 FOR
 SELECT Id,ItemsToFetch
 FROM #Suppliers
 OPEN Supplier_Cursor
 FETCH NEXT FROM Supplier_Cursor INTO @Id,@ItemsToFetch;
 WHILE @@FETCH_STATUS = 0
 BEGIN
 -- Only grab unique BatchValue from the top of the queue
 INSERT INTO #Batch(BatchId,QueueId,BatchValue,SupplierId)
 SELECT top(@ItemsToFetch) MIN(qi.BatchId) as BatchId,MIN(qi.Id) as QueueId,im.MetaValue AS BatchValue,i.SupplierId
 FROM [Queue].[QueueItem] qi 
 inner join [Batch].[BatchMetadata] im on im.BatchId=qi.BatchId
 inner join [Batch].[Batch] i on i.Id=im.BatchId
 WHERE i.SupplierId=@Id and im.MetaValue not in (select BatchValue from #Batch)
 GROUP BY i.SupplierId,im.MetaValue
 order by QueueId; -- remove this after testing passed
 FETCH NEXT FROM Supplier_Cursor INTO @Id,@ItemsToFetch;
 END;
 CLOSE Supplier_Cursor;
 DEALLOCATE Supplier_Cursor;
 DROP TABLE #Suppliers;
 -- return final list
 SELECT * from #Batch
 DROP TABLE #Batch;
 COMMIT
END TRY
BEGIN CATCH
 Print 'Error from within Transaction';
 IF @@TRANCOUNT >0 ROLLBACK TRAN
END CATCH
END

• JNK: What RDBMS? In SQL Server this should be easily done with CTEs and windowing functions like ROW_NUMBER().
• Chris Woods: Can you share the current procedure?
• Geoffrey Hall: @JNK Version is Microsoft SQL Server 2012 (SP1) - 11.0.3401.0 (X64)

Answer 1

I think something with CROSS APPLY would work well for you. Look at this example:

DECLARE @tbl_Supplier TABLE (ID INT PRIMARY KEY, SupplierName VARCHAR(25),Weighting FLOAT)
INSERT INTO @tbl_Supplier
VALUES (1,'Supplier1',.5),(2,'Supplier2',.25 ),(3,'Supplier3',.25);
DECLARE @Items TABLE(SupplierID INT,ItemName VARCHAR(25))
INSERT INTO @Items
VALUES (1,'Item1'),(1,'Item2'),(1,'Item3'),(2,'Item1'),(2,'Item2'),(3,'Item2'),(3,'Item3');
DECLARE @TotalBatchSize INT = 4;
SELECT SupplierName,
 CA.ItemName,
 S.Weighting * @TotalBatchSize AS num_of_rows_to_be_returned
FROM @tbl_Supplier AS S
CROSS APPLY (SELECT TOP(CAST(@TotalBatchSize * S.Weighting AS INT)) ItemName FROM @Items I WHERE I.SupplierID = S.ID) CA

Results:

SupplierName ItemName num_of_rows_to_be_returned
------------------------- ------------------------- --------------------------
Supplier1 Item1 2
Supplier1 Item2 2
Supplier2 Item1 1
Supplier3 Item2 1