SQL Server – How to Replace a Cursor with a Single Query

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