OK, I fixed it on my system. I had Nop_Product rows without corresponding Nop_ProductVariant. So I deleted the Nop_Product rows that didn't exist in Nop_ProductVariant. I did try it in a test database before my pre-production database. :)
It was kludgy, but it worked....
Thank you for the soild feedback, I still can't get that approch to work on my DB.
try to update this Sp: (this should solve the problem)
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[Nop_ProductVariantLoadAll] ( @CategoryID int = 0, @ManufacturerID int = 0, @Keywords nvarchar(MAX), @ShowHidden bit = 0, @PageIndex int = 0, @PageSize int = 2147483644, @TotalRecords int = null OUTPUT ) AS BEGIN
--init SET @Keywords = isnull(@Keywords, '') SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'
--display order CREATE TABLE #DisplayOrder ( ProductID int not null PRIMARY KEY, DisplayOrder int not null )
IF @CategoryID IS NOT NULL AND @CategoryID > 0 BEGIN INSERT #DisplayOrder SELECT pcm.ProductID, pcm.DisplayOrder FROM [Nop_Product_Category_Mapping] pcm WHERE pcm.CategoryID = @CategoryID END ELSE IF @ManufacturerID IS NOT NULL AND @ManufacturerID > 0 BEGIN INSERT #DisplayOrder SELECT pmm.ProductID, pmm.Displayorder FROM [Nop_Product_Manufacturer_Mapping] pmm WHERE pmm.ManufacturerID = @ManufacturerID END ELSE BEGIN INSERT #DisplayOrder SELECT p.ProductID, 1 FROM [Nop_Product] p ORDER BY p.[Name] END
--paging DECLARE @PageLowerBound int DECLARE @PageUpperBound int DECLARE @RowsToReturn int
SET @RowsToReturn = @PageSize * (@PageIndex + 1) SET @PageLowerBound = @PageSize * @PageIndex SET @PageUpperBound = @PageLowerBound + @PageSize + 1
CREATE TABLE #PageIndex ( IndexID int IDENTITY (1, 1) NOT NULL, ProductVariantID int NOT NULL, DisplayOrder int NOT NULL, ) INSERT INTO #PageIndex (ProductVariantID, DisplayOrder) SELECT DISTINCT isnull(pv.ProductVariantID,0) ProductVariantID -- pv.ProductVariantID , do.DisplayOrder FROM Nop_Product p with (NOLOCK) LEFT OUTER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID LEFT OUTER JOIN Nop_Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.ProductID=pmm.ProductID LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID JOIN #DisplayOrder do on p.ProductID = do.ProductID WHERE ( ( @ShowHidden = 1 OR p.Published = 1 ) AND ( @ShowHidden = 1 OR pv.Published = 1 ) AND ( p.Deleted=0 ) AND ( @CategoryID IS NULL OR @CategoryID=0 OR pcm.CategoryID=@CategoryID ) AND ( @ManufacturerID IS NULL OR @ManufacturerID=0 OR pmm.ManufacturerID=@ManufacturerID ) AND ( -- search standard content patindex(@Keywords, isnull(p.name, '')) > 0 or patindex(@Keywords, isnull(pv.name, '')) > 0 or patindex(@Keywords, isnull(pv.sku , '')) > 0 ) AND ( @ShowHidden = 1 OR (getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999')) ) ) ORDER BY do.DisplayOrder
--total records SET @TotalRecords = @@rowcount SET ROWCOUNT @RowsToReturn
--return SELECT pv.* FROM #PageIndex [pi] INNER JOIN Nop_ProductVariant pv on pv.ProductVariantID = [pi].ProductVariantID WHERE [pi].IndexID > @PageLowerBound AND [pi].IndexID < @PageUpperBound AND [pi].ProductVariantID <> 0 ORDER BY IndexID
SET ROWCOUNT 0
DROP TABLE #DisplayOrder DROP TABLE #PageIndex END
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.