nopCommerce import products via SQL: The multi-part identifier "" could not be bound

Situation

We have a nopCommerce import products process from external xml files, we have the following stored procedure that do bulk queries in order to fulfill our goal.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SynchronizeProducts]') AND type in ('P'))
DROP PROCEDURE [dbo].[SynchronizeProducts]
GO

CREATE PROCEDURE [dbo].[SynchronizeProducts] (
  @supplierId int = 0
    , @warehouseId int = 0
    , @manufacturerId int = 0
    , @localCurrencyId int = 0
    -- Manufacturer Settings
    , @updateStock bit = 0
    , @updatePrice bit = 0
    , @updateOldPrice bit = 0
    , @autoUnpublish bit = 0
    , @autoRepublish bit = 0
    , @autoResetStock bit = 0
    , @notReturnable bit = 0
    , @enableBackorder bit = 0
    , @displayAvailability bit = 0
    , @backorderCategoryId int = 0
    , @backorderWarehouseId int = 0
    , @defaultDeliveryDateId int = 0
    , @backorderDeliveryDateId int = 0
    , @syncVersion int = 0
    , @stockCount int = 0 OUTPUT
)
AS
BEGIN
    DECLARE @STOCK_MANAGE_BY_ATTR int = 2;
    DECLARE @BACKORDER_ENABLE int = 1;
    DECLARE @BACKORDER_DISABLE int = 0;

    CREATE TABLE #STK (Id int not null
        , SupplierId int not null
        , ProductId int null
        , CombinationId int null
        , Quantity int not null
        , Currency nvarchar(5)
        , FP decimal(18, 4)
        , FOP decimal(18, 4)
        , Price decimal(18, 4)
        , OldPrice decimal(18, 4)
        , Synchronized bit not null
        , RowNum int not null
    );

    CREATE TABLE #MISSING_PRODUCTS (Id int not null);

    -------------------------------------------------------------
    -->> POPULATE #STK / #MISSING_PRODUCTS Tables
    -------------------------------------------------------------
    BEGIN                       
        INSERT INTO #STK
        SELECT STK.Id
            , SupplierId
            , STK.ProductId
            , CombinationId
            , Quantity
            , Currency
            , STK.Price FP
            , STK.OldPrice FOP
            , (Price * LC.Rate / SC.Rate) Price
            , (OldPrice * LC.Rate / SC.Rate) OldPrice
            , Synchronized
            , ROW_NUMBER() OVER(PARTITION BY STK.ProductId ORDER BY (Price * LC.Rate / SC.Rate) ASC) AS RowNum
        FROM Stock STK
        LEFT JOIN Currency LC ON LC.Id = @localCurrencyId
        LEFT JOIN Currency SC ON SC.CurrencyCode = Currency
        INNER JOIN Product_Manufacturer_Mapping PMM ON PMM.ProductId = STK.ProductId
        WHERE STK.SupplierId = @supplierId
            AND PMM.ManufacturerId = @manufacturerId
            AND STK.Synchronized = 0 AND STK.ProductId > 0
            AND STK.[Disabled] = 0
    END

    BEGIN
        INSERT INTO #MISSING_PRODUCTS
        SELECT STK.ProductId
        FROM Stock STK 
        INNER JOIN Product_Manufacturer_Mapping PMM ON PMM.ManufacturerId = @manufacturerId AND PMM.ProductId = STK.ProductId
        WHERE  STK.SupplierId = @supplierId AND STK.Synchronized = 1 AND STK.[Disabled] = 0
    END


    -------------------------------------------------------------
    -->> UPDATE COMBINATIONS
    -------------------------------------------------------------
    BEGIN                       
        UPDATE PAC SET 
            StockQuantity = (
                CASE
                    WHEN (@updateStock = 1 AND #STK.Quantity >= 0) THEN #STK.Quantity
                    ELSE PAC.StockQuantity
                END
            ),
            OverriddenPrice = (
                CASE
                    WHEN (@updatePrice = 1 AND #STK.Price > 0) THEN #STK.Price
                    ELSE PAC.OverriddenPrice
                END
            )
        FROM ProductAttributeCombination PAC
        INNER JOIN #STK ON PAC.Id = #STK.CombinationId 
                AND PAC.ProductId = #STK.ProductId
    END

    -------------------------------------------------------------
    -->> CREATE PRODUCTS (MISSING) WAREHOUSE
    -------------------------------------------------------------
    INSERT INTO ProductWarehouseInventory (ProductId, WarehouseId, StockQuantity, ReservedQuantity)
    SELECT DISTINCT ProductId, @warehouseId, 0, 0
    FROM #STK
    WHERE ProductId NOT IN (SELECT ProductId FROM ProductWarehouseInventory WHERE WarehouseId = @warehouseId )

    -------------------------------------------------------------
    -->> UPDATE PRODUCTS WAREHOUSE
    -------------------------------------------------------------
    IF @updateStock = 1         
    BEGIN 
        UPDATE W SET
            StockQuantity = --#STK.Quantity
            (
                CASE
                    WHEN (W.StockQuantity - W.ReservedQuantity) < 0 THEN W.StockQuantity
                    ELSE #STK.Quantity
                END
            )
        FROM ProductWarehouseInventory W
        INNER JOIN (
            SELECT  ProductId, SUM(Quantity) Quantity
            FROM #STK
            WHERE CombinationId IS NULL
            GROUP BY ProductId
        ) AS Q ON W.ProductId = Q.ProductId AND W.WarehouseId = @warehouseId
    END

    -------------------------------------------------------------
    --### UPDATE PRODUCTS STOCK
    -------------------------------------------------------------
    IF @updateStock = 1         
    BEGIN 
        UPDATE P SET
            StockQuantity = #STK.Quantity
        FROM Product P
        INNER JOIN #STK ON P.Id = #STK.ProductId AND #STK.CombinationId IS NULL
        WHERE P.ManageInventoryMethodId != @STOCK_MANAGE_BY_ATTR
    END

    -------------------------------------------------------------
    --### UPDATE PRODUCTS PRICE
    -------------------------------------------------------------
    BEGIN                       
        UPDATE P SET
            Price = (
                CASE
                    WHEN (@updatePrice = 1 AND #STK.Price > 0) THEN #STK.Price
                    ELSE P.Price
                END
            ),
            OldPrice = (
                CASE
                    WHEN (@updateOldPrice = 1) THEN COALESCE(#STK.OldPrice, 0)
                    ELSE P.OldPrice
                END
            )
        FROM Product P
        INNER JOIN #STK ON P.Id = #STK.ProductId
        WHERE #STK.RowNum = 1
    END

    -------------------------------------------------------------
    --### UPDATE PRODUCT PRICE TABLE
    -------------------------------------------------------------

    IF @updatePrice = 1
    BEGIN
        UPDATE SP SET 
            StockId = #STK.Id
            , Currency = #STK.Currency
            , Price = FP
            , OldPrice = FOP
        FROM StockPrice SP
        INNER JOIN #STK ON #STK.ProductId = SP.ProductId AND #STK.FP > 0 AND RowNum = 1
    END

    IF @updatePrice = 1
    BEGIN
        UPDATE SP SET 
            StockId = #STK.Id
            , Currency = #STK.Currency
            , Price = FP
            , OldPrice = FOP
        FROM StockPrice SP
        INNER JOIN #STK ON #STK.ProductId = SP.ProductId AND #STK.CombinationId = SP.CombinationId AND #STK.FP > 0
        WHERE COALESCE(#STK.CombinationId, 0) != 0
    END

    IF @updatePrice = 1
    BEGIN
        INSERT INTO StockPrice (ProductId, StockId, Currency, Price, OldPrice)
        SELECT ProductId, Id, Currency, FP, FOP
        FROM #STK 
        WHERE ProductId NOT IN (SELECT ProductId FROM StockPrice) AND #STK.FP > 0 AND RowNum = 1
    END

    IF @updatePrice = 1
    BEGIN
        INSERT INTO StockPrice (ProductId, CombinationId, StockId, Currency, Price, OldPrice)
        SELECT ProductId, CombinationId, Id, Currency, FP, FOP
        FROM #STK 
        WHERE CombinationId IS NOT NULL AND #STK.FP > 0
              AND CombinationId NOT IN (SELECT CombinationId FROM StockPrice WHERE CombinationId IS NOT NULL) 
    END

    -------------------------------------------------------------
    --### MISSING PRODUCTS >> AUTO RESET COMBINATION
    -------------------------------------------------------------
    IF @autoResetStock = 1      
    BEGIN
        UPDATE ProductAttributeCombination SET StockQuantity = 0
        WHERE ProductId IN (SELECT Id FROM #MISSING_PRODUCTS)
    END

    -------------------------------------------------------------
    --### MISSING PRODUCTS >> AUTO RESET WAREHOUSE
    -------------------------------------------------------------
    IF @autoResetStock = 1      
    BEGIN
        UPDATE ProductWarehouseInventory SET StockQuantity = 0
        WHERE WarehouseId = @warehouseId AND ProductId IN (SELECT Id FROM #MISSING_PRODUCTS)
    END

    -------------------------------------------------------------
    -- UPDATE PRODUCTS TOTAL STOCK / PAC
    -------------------------------------------------------------
    IF @updateStock = 1 OR  @autoResetStock = 1     
    BEGIN
        UPDATE P SET StockQuantity = T.StockQuantity
        FROM Product P
        INNER JOIN (
            SELECT ProductId, SUM(StockQuantity) AS StockQuantity
            FROM ProductAttributeCombination
            WHERE (@updateStock = 1 AND ProductId IN (SELECT DISTINCT ProductId FROM #STK))
                OR (@autoResetStock = 1 AND ProductId IN (SELECT Id FROM #MISSING_PRODUCTS ))
            GROUP BY ProductId
        ) AS T ON T.ProductId = P.Id
        WHERE P.ManageInventoryMethodId = @STOCK_MANAGE_BY_ATTR
    END

    -------------------------------------------------------------
    -- UPDATE PRODUCTS TOTAL STOCK / WAREHOUSE
    -------------------------------------------------------------
    IF @updateStock = 1 OR  @autoResetStock = 1     
    BEGIN
        UPDATE P SET StockQuantity = T.StockQuantity - T.ReservedQuantity
        FROM Product P
        INNER JOIN (
            SELECT ProductId, SUM(StockQuantity) AS StockQuantity, SUM(ReservedQuantity) AS ReservedQuantity
            FROM ProductWarehouseInventory
            WHERE  
                (
                    (@updateStock = 1 AND ProductId IN (SELECT DISTINCT ProductId FROM #STK))
                    OR (@autoResetStock = 1 AND ProductId IN (SELECT Id FROM #MISSING_PRODUCTS ))
                )
            GROUP BY ProductId
        ) AS T ON T.ProductId = P.Id
        WHERE P.ManageInventoryMethodId != @STOCK_MANAGE_BY_ATTR
    END

    -------------------------------------------------------------
    -- UPDATE PRODUCTS TOTAL STOCK
    -------------------------------------------------------------
    IF @autoResetStock = 1      
    BEGIN
        UPDATE Product SET StockQuantity = 0
        WHERE Id IN (SELECT Id FROM #MISSING_PRODUCTS)
    END

    -------------------------------------------------------------
    -- UPDATE PRODUCTS ATTRIBUTES
    -------------------------------------------------------------
    BEGIN                       
        UPDATE Product SET
            Published = (
                CASE 
                    WHEN (@autoRepublish = 1 AND StockQuantity > 0) THEN 1
                    WHEN (@autoUnpublish = 1 AND StockQuantity <= 0 AND @enableBackorder = 0) THEN 0 
                    ELSE Published
                END
            ),
            DisplayStockAvailability = (
                CASE
                    WHEN (StockQuantity > 0) THEN 1 
                    WHEN (@enableBackorder = 1) THEN @displayAvailability
                    ELSE 0
                END
            ),
            BackorderModeId = (
                CASE
                    WHEN (StockQuantity > 0) THEN @BACKORDER_DISABLE 
                    WHEN (@enableBackorder = 1) THEN @BACKORDER_ENABLE 
                    ELSE @BACKORDER_DISABLE
                END
            ),
            WarehouseId = (
                CASE
                    WHEN (StockQuantity > 0) THEN @warehouseId
                    WHEN (@enableBackorder = 1) THEN @backorderWarehouseId
                    ELSE 0
                END
            ),
            DeliveryDateId = (
                CASE
                    WHEN (StockQuantity > 0) THEN @defaultDeliveryDateId 
                    WHEN (@enableBackorder = 1) THEN @backorderDeliveryDateId 
                    ELSE DeliveryDateId
                END
            ),
            UseMultipleWarehouses = (
                CASE
                    WHEN (StockQuantity > 0) THEN 1 
                    WHEN @enableBackorder = 1 THEN 0 
                    ELSE UseMultipleWarehouses
                END
            ),
            NotReturnable = (
                CASE
                    WHEN (StockQuantity > 0) THEN 0 
                    WHEN @enableBackorder = 1 THEN @notReturnable
                    ELSE NotReturnable
                END
            ),
            UpdatedOnUtc = GETUTCDATE()     
        WHERE Id IN (
            SELECT ProductId 
            FROM Product_Manufacturer_Mapping 
            WHERE ManufacturerId = @manufacturerId
        )
    END

    -------------------------------------------------------------
    -- UPDATE STOCK
    -------------------------------------------------------------
    BEGIN
        UPDATE Stock SET Synchronized = 1, [Version] = @syncVersion
        WHERE Id IN (SELECT Id FROM #STK)

        UPDATE Stock SET Quantity = 0
        WHERE ProductId IN (SELECT Id FROM #MISSING_PRODUCTS)
    END

    -------------------------------------------------------------
    -- PROCESS BACKORDER CATEGORY
    -------------------------------------------------------------
    BEGIN
        DELETE FROM Product_Category_Mapping
        WHERE CategoryId = @backorderCategoryId 
            AND ProductId IN (SELECT Id FROM Product WHERE StockQuantity > 0 OR BackorderModeId = @BACKORDER_DISABLE)
    END
    BEGIN
        INSERT INTO Product_Category_Mapping(CategoryId, ProductId, IsFeaturedProduct, DisplayOrder)
        SELECT @backorderCategoryId , Id, 0, 0
        FROM Product
        WHERE StockQuantity <= 0 AND BackorderModeId = @BACKORDER_ENABLE
            AND Id NOT IN ( SELECT ProductId FROM Product_Category_Mapping WHERE CategoryId = @backorderCategoryId)
    END

    -------------------------------------------------------------
    -- END
    -------------------------------------------------------------

    SELECT @stockCount = COUNT(*) FROM #STK;
    SELECT * FROM #STK ORDER BY ProductId DESC;

    DROP TABLE #STK;
    DROP TABLE #MISSING_PRODUCTS;
END
GO

The issue

System.Data.SqlClient.SqlException (0x80131904): The multi-part identifier "#STK.Quantity" could not be bound.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlCommand(DatabaseFacade databaseFacade, RawSqlString sql, IEnumerable`1 parameters)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlCommand(DatabaseFacade databaseFacade, RawSqlString sql, Object[] parameters)

...

ClientConnectionId:32bdc194-3328-46d6-84b4-c59a6a310da9
Error Number:4104,State:1,Class:16

Investigation

We have found the following link it may relates to this issue https://radacad.com/fixing-the-error-the-multi-part-identifier-could-not-be-bound-in-join-statements especially when the issue of multipart described here https://prnt.sc/s3rbgu.

At first can we have an initial reason for this issue, in order to decide how to solve it.