I also removed the sorting options that take into account the display order set in the product category mapping, product manufacturer mapping and related product tables since I'm trying to optimize the query to work in shared hosting environments, so this versions does sort by Position, Name, Price, and CreatedOn, it works with the combo box selection of the category template:
-- =============================================
-- Author: www.bitstarsolutions.com - Tudor Carean
-- =============================================
CREATE PROCEDURE [dbo].[Nop_ProductLoadAllPaged]
(
@CategoryID int = 0,
@ManufacturerID int = 0,
@ProductTagID int = 0,
@FeaturedProducts bit = null, --0 featured only , 1 not featured only, null - load all products
@PriceMin money = null,
@PriceMax money = null,
@RelatedToProductID int = 0,
@Keywords nvarchar(MAX) = '',
@SearchDescriptions bit = 0,
@ShowHidden bit = 0,
@PageIndex int = 0,
@PageSize int = 20, --2147483644,
@FilteredSpecs nvarchar(300) = null, --filter by attributes (comma-separated list). e.g. 14,15,16
@LanguageID int = 7, --0,
@OrderBy int = 0, --0 position, 5 - Name, 10 - Price, 15 - CreatedOn DESC
@TotalRecords int = null OUTPUT
)
AS
BEGIN
--init
DECLARE @SearchKeywords bit
SET @SearchKeywords = 1
IF (@Keywords IS NULL OR @Keywords = N'')
SET @SearchKeywords = 0
SET @Keywords = isnull(@Keywords, '')
SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'
SET @PriceMin = isnull(@PriceMin, 0)
SET @PriceMax = isnull(@PriceMax, 2147483644)
--DECLARE @PageIndexTable TABLE
CREATE TABLE #PageIndexTable
(
[ProductID] int NOT NULL,
[total] int
)
--filter by attributes
SET @FilteredSpecs = isnull(@FilteredSpecs, '')
CREATE TABLE #FilteredSpecs
(
SpecificationAttributeOptionID int not null
)
INSERT INTO #FilteredSpecs (SpecificationAttributeOptionID)
SELECT CAST(data as int) FROM dbo.[NOP_splitstring_to_table](@FilteredSpecs, ',');
DECLARE @SpecAttributesCount int
SELECT @SpecAttributesCount = COUNT(1) FROM #FilteredSpecs
--paging
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1;
WITH DispOrderCTE (ProductIDCTE, /*RowNumber,*/ Name, Price, CreatedOn, total) AS
(
SELECT DISTINCT
p.ProductID,
--ROW_NUMBER() OVER (ORDER BY p.ProductID),
p.Name,
pv.Price,
p.CreatedOn,
COUNT(*) OVER(PARTITION BY NULL)
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_ProductTag_Product_Mapping ptpm with (NOLOCK) ON p.ProductID=ptpm.ProductID
LEFT OUTER JOIN Nop_RelatedProduct rp with (NOLOCK) ON p.ProductID=rp.ProductID2
LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID
LEFT OUTER JOIN Nop_ProductVariantLocalized pvl with (NOLOCK) ON pv.ProductVariantID = pvl.ProductVariantID AND pvl.LanguageID = @LanguageID
LEFT OUTER JOIN Nop_ProductLocalized pl with (NOLOCK) ON p.ProductID = pl.ProductID AND pl.LanguageID = @LanguageID
WHERE
(
(
@CategoryID IS NULL OR @CategoryID=0
OR (pcm.CategoryID=@CategoryID AND (@FeaturedProducts IS NULL OR pcm.IsFeaturedProduct=@FeaturedProducts))
)
AND (
@ManufacturerID IS NULL OR @ManufacturerID=0
OR (pmm.ManufacturerID=@ManufacturerID AND (@FeaturedProducts IS NULL OR pmm.IsFeaturedProduct=@FeaturedProducts))
)
AND (
@ProductTagID IS NULL OR @ProductTagID=0
OR ptpm.ProductTagID=@ProductTagID
)
AND (
@RelatedToProductID IS NULL OR @RelatedToProductID=0
OR rp.ProductID1=@RelatedToProductID
)
AND (
@ShowHidden = 1 OR p.Published = 1
)
AND
(
p.Deleted=0
)
AND
(
@ShowHidden = 1 OR pv.Published = 1
)
AND
(
@ShowHidden = 1 OR pv.Deleted = 0
)
AND (
@PriceMin IS NULL OR @PriceMin=0
OR pv.Price > @PriceMin
)
AND (
@PriceMax IS NULL OR @PriceMax=2147483644 -- max value
OR pv.Price < @PriceMax
)
AND (
@SearchKeywords = 0 or
(
-- search standard content
patindex(@Keywords, p.name) > 0
or patindex(@Keywords, pv.name) > 0
or patindex(@Keywords, pv.sku) > 0
or (@SearchDescriptions = 1 and patindex(@Keywords, p.ShortDescription) > 0)
or (@SearchDescriptions = 1 and patindex(@Keywords, p.FullDescription) > 0)
or (@SearchDescriptions = 1 and patindex(@Keywords, pv.Description) > 0)
-- search language content
or patindex(@Keywords, pl.name) > 0
or patindex(@Keywords, pvl.name) > 0
or (@SearchDescriptions = 1 and patindex(@Keywords, pl.ShortDescription) > 0)
or (@SearchDescriptions = 1 and patindex(@Keywords, pl.FullDescription) > 0)
or (@SearchDescriptions = 1 and patindex(@Keywords, pvl.Description) > 0)
)
)
AND
(
@ShowHidden = 1
OR
(getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999'))
)
AND
(
--filter by specs
@SpecAttributesCount = 0
OR
(
NOT EXISTS(
SELECT 1
FROM #FilteredSpecs [fs]
WHERE [fs].SpecificationAttributeOptionID NOT IN (
SELECT psam.SpecificationAttributeOptionID
FROM dbo.Nop_Product_SpecificationAttribute_Mapping psam
WHERE psam.AllowFiltering = 1 AND psam.ProductID = p.ProductID
)
)
)
)
)
)
INSERT INTO #PageIndexTable (ProductId,total)
SELECT templist.ProductIDCTE,templist.total from
(select fulllist.ProductIDCTE,
fulllist.total,
ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @OrderBy = 0
THEN fulllist.ProductIDCTE END ASC,
CASE WHEN @OrderBy = 5
THEN fulllist.Name END ASC,
CASE WHEN @OrderBy = 10
THEN fulllist.Price END ASC,
CASE WHEN @OrderBy = 15
THEN fulllist.CreatedOn END DESC
) as RowNumber
from DispOrderCTE fulllist
) templist
WHERE
RowNumber > @PageLowerBound AND
RowNumber < @PageUpperBound
ORDER BY
RowNumber
SELECT
p.ProductId,
p.Name,
p.ShortDescription,
p.FullDescription,
p.AdminComment,
p.TemplateId,
p.ShowOnHomePage,
p.MetaKeywords,
p.MetaDescription,
p.MetaTitle,
p.SEName,
p.AllowCustomerReviews,
p.AllowCustomerRatings,
p.RatingSum,
p.TotalRatingVotes,
p.Published,
p.Deleted,
p.CreatedOn,
p.UpdatedOn
FROM
#PageIndexTable [pi]
INNER JOIN Nop_Product p on p.ProductID = [pi].ProductID
if ((select COUNT(*) from #PageIndexTable)>0)
SELECT @TotalRecords=max(total) from #PageIndexTable
else
select @TotalRecords = 0
SET ROWCOUNT 0
DROP TABLE #FilteredSpecs
DROP TABLE #PageIndexTable
END
GO
If I manage to optimize it for shared hosting environments I'll post the latest version here.