nopCommerce分页算法(sql server 存储过程)
Code
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Nop_ProductSearch]
@Keywords NVARCHAR(MAX),
@SearchDescriptions bit = 0,
@PageNumber INT = 1,
@PageSize INT = 20,
@ShowHidden bit = 0,
@TotalProducts INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
IF @PageSize is null or @PageSize = 0
SET @PageSize = 20
SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'
CREATE TABLE #ProductFilter
(
ProductFilterID int IDENTITY (1, 1) NOT NULL,
ProductID int not null
)
IF ((@Keywords IS NOT NULL) AND (LEN(@Keywords) > 0))
BEGIN
INSERT #ProductFilter (ProductID)
SELECT distinct p.ProductID
FROM
Nop_Product p with (NOLOCK)
left join Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID
WHERE
(
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)
)
and (p.Published = 1 or @ShowHidden = 1)
and (pv.Published = 1 or @ShowHidden = 1)
and p.Deleted = 0
and pv.Deleted = 0
END
SELECT @TotalProducts = COUNT(ProductID)
FROM #ProductFilter
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@PageNumber-1) * @PageSize
SELECT @LastRec = (@PageNumber * @PageSize + 1)
SELECT p.*
FROM
Nop_Product p with (NOLOCK)
inner join #ProductFilter pf with (NOLOCK) ON p.ProductID = pf.ProductID
WHERE pf.ProductFilterID > @FirstRec AND pf.ProductFilterID < @LastRec
ORDER BY p.Name
DROP TABLE #ProductFilter
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Nop_ProductSearch]
@Keywords NVARCHAR(MAX),
@SearchDescriptions bit = 0,
@PageNumber INT = 1,
@PageSize INT = 20,
@ShowHidden bit = 0,
@TotalProducts INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
IF @PageSize is null or @PageSize = 0
SET @PageSize = 20
SET @Keywords = '%' + rtrim(ltrim(@Keywords)) + '%'
CREATE TABLE #ProductFilter
(
ProductFilterID int IDENTITY (1, 1) NOT NULL,
ProductID int not null
)
IF ((@Keywords IS NOT NULL) AND (LEN(@Keywords) > 0))
BEGIN
INSERT #ProductFilter (ProductID)
SELECT distinct p.ProductID
FROM
Nop_Product p with (NOLOCK)
left join Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID
WHERE
(
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)
)
and (p.Published = 1 or @ShowHidden = 1)
and (pv.Published = 1 or @ShowHidden = 1)
and p.Deleted = 0
and pv.Deleted = 0
END
SELECT @TotalProducts = COUNT(ProductID)
FROM #ProductFilter
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@PageNumber-1) * @PageSize
SELECT @LastRec = (@PageNumber * @PageSize + 1)
SELECT p.*
FROM
Nop_Product p with (NOLOCK)
inner join #ProductFilter pf with (NOLOCK) ON p.ProductID = pf.ProductID
WHERE pf.ProductFilterID > @FirstRec AND pf.ProductFilterID < @LastRec
ORDER BY p.Name
DROP TABLE #ProductFilter
END