USE [GeneralMobil] GO /****** Object: StoredProcedure [dbo].[Nop_ProductLoadAllPaged] Script Date: 07/11/2009 18:09:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Nop_ProductLoadAllPaged] ( @CategoryID int, @ManufacturerID int, @FeaturedProducts bit = null, --0 featured only , 1 not featured only, null - load all products @PriceMin money = null, @PriceMax money = null, @Keywords nvarchar(MAX), @SearchDescriptions bit = 0, @ShowHidden bit, @PageIndex int = 0, @PageSize int = 2147483644, @TotalRecords int = null OUTPUT ) AS BEGIN
SET @Keywords = isnull(@Keywords, '') SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'
SET @PriceMin = isnull(@PriceMin, 0) SET @PriceMax = isnull(@PriceMax, 2147483644)
--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, ProductID int NOT NULL, DisplayOrder int NOT NULL, ProductName nvarchar(400) NOT NULL )
INSERT INTO #PageIndex (ProductID, DisplayOrder, ProductName) SELECT DISTINCT p.ProductID, do.DisplayOrder, p.Name 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 ( ( p.Published = 1 OR @ShowHidden = 1 ) AND ( pv.Published = 1 or @ShowHidden = 1 ) AND ( p.Deleted=0 ) AND ( @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 ( pv.Price BETWEEN @PriceMin AND @PriceMax ) AND ( patindex(@Keywords, isnull(p.name, '')) > 0 or patindex(@Keywords, isnull(pv.name, '')) > 0 or patindex(@Keywords, isnull(pv.sku , '')) > 0 or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.ShortDescription, '')) > 0) or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(p.FullDescription, '')) > 0) or (@SearchDescriptions = 1 and patindex(@Keywords, isnull(pv.Description, '')) > 0) ) ) ORDER BY p.Name
SET @TotalRecords = @@rowcount SET ROWCOUNT @RowsToReturn
SELECT p.* FROM #PageIndex [pi] INNER JOIN Nop_Product p on p.ProductID = [pi].ProductID WHERE [pi].IndexID > @PageLowerBound AND [pi].IndexID < @PageUpperBound ORDER BY IndexID
SET ROWCOUNT 0
DROP TABLE #PageIndex END
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.