sql server – Need Ideas on how to eliminate this scalar funciton or make it faster?

I’m working on pretty lengthy view and one of SQL statements calls a scalar function, which really degrades the performance. The following is the function that is called in a select statement. (sql server 2016)

Really not sure the best ways to modify this process.

Simplified select statement.

Select vendorpartid, 
dbo.ufn_StockUomQuantityToOrder(vpp.priorityLevel, vpp.monthlyUsageRate, vpp.minimumPurchaseUomOrderQuantity, vpp.purchaseUomConversionFactor, vpp.orderFrequencyDays, vpp.boxQuantity) stockUomQuantityToOrder
FROM vendorpartpriority vpp


ALTER FUNCTION (dbo).(ufn_StockUomQuantityToOrder) (
            @priorityLevel decimal(38, 10),
            @monthlyUsageRate decimal(38,10),
            @minimumPurchaseUomOrderQuantity decimal(38, 10),
            @purchaseUomConversion decimal(38, 10),
            @orderFrequencyDays decimal(38,10),
            @boxQuantity int
        )  
        RETURNS int
        WITH SCHEMABINDING
        AS   
        -- Calculate the quantity that needs to be ordered  
        BEGIN  
            DECLARE @quantityToOrderDecimal decimal(38, 10) = NULL;
            DECLARE @quantityToOrderInt int = NULL;
            DECLARE @orderFrequencyMinimumQuantity decimal(38, 10) = NULL;
            DECLARE @minimumStockUomOrderQuantity decimal(38,10) = NULL;
            
            --set the default order quantity
            SELECT @quantityToOrderDecimal = (-1.0 * @monthlyUsageRate * @priorityLevel);
            
            --get the minimum order quantity in stock UOM
            SELECT @minimumStockUomOrderQuantity = (@minimumPurchaseUomOrderQuantity * @purchaseUomConversion);
        
            --calculate the order frequency minimum
            IF(@orderFrequencyDays IS NOT NULL AND @monthlyUsageRate IS NOT NULL)
                SELECT @orderFrequencyMinimumQuantity = (@monthlyUsageRate * @orderFrequencyDays / 30.0);
                    
            --do we need to meet a vendor minimum
            IF (@quantityToOrderDecimal IS NULL OR @quantityToOrderDecimal < @minimumStockUomOrderQuantity)
                SELECT @quantityToOrderDecimal = @minimumStockUomOrderQuantity;
            
            --do we need to meet an order frequency minimum
            IF (@quantityToOrderDecimal IS NULL OR @quantityToOrderDecimal < @orderFrequencyMinimumQuantity)
                SELECT @quantityToOrderDecimal = @orderFrequencyMinimumQuantity;
            
            --convert to the int
            SELECT @quantityToOrderInt = CAST(CEILING(@quantityToOrderDecimal) AS int);
                
            --did we come up with a number that needs to be an increment of boxQuantity
            IF(@quantityToOrderInt IS NOT NULL AND @boxQuantity > 0)
                BEGIN
                    --get the partial box quantity if any
                    DECLARE @partialBox int = @quantityToOrderInt % @boxQuantity;
                    
                    --remove the partial box and add a full one (if we are not of box increments)
                    IF(@partialBox <> 0)
                    BEGIN
                        SELECT @quantityToOrderInt = (@quantityToOrderInt - @partialBox + @boxQuantity);
                    END
                END 
        
            RETURN @quantityToOrderInt;  
        END;
        
GO

2019 isn’t an option now. I have dabbled with the in-line table value, but I’m not quite sure how to get rid of the declares.

I’m not 100% sure on the nulls. My gut says I shouldn’t be passing any values that are nulls, but looks like input table has quite a few in different columns.

Right now I’m trying to make some glaring performance changes until we get time to dismantle the whole process.