I ve probably forgot this fonction.
I ve'nt solve it, but i ll do asap.
ALTER PROCEDURE [dbo].[Nop_ProductSearch]
(
@Keywords NVARCHAR(MAX),
@SearchDescriptions bit = 0,
@PageNumber INT = 1,
@PageSize INT = 20,
@ShowHidden bit = 0,
@TotalProducts INT OUTPUT,
@Language VARCHAR(10)
)
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].[ProductId]
,[Nop_Translate_Product].[Name]
,[Nop_Translate_Product].[ShortDescription]
,[Nop_Translate_Product].[FullDescription]
,[p].[AdminComment]
,[p].[ProductTypeID]
,[p].[TemplateID]
,[p].[ShowOnHomePage]
,[Nop_Translate_Product].[MetaKeywords]
,[Nop_Translate_Product].[MetaDescription]
,[Nop_Translate_Product].[MetaTitle]
,[Nop_Translate_Product].[SEName]
,[p].[AllowCustomerReviews]
,[p].[RatingSum]
,[p].[TotalRatingVotes]
,[p].[Published]
,[p].[Deleted]
,[p].[CreatedOn]
,[p].[UpdatedOn]
FROM [Nop_Product] p with (NOLOCK)
inner join #ProductFilter pf with (NOLOCK) ON p.ProductID = pf.ProductID
INNER JOIN Nop_Translate_Product ON Nop_Translate_Product.Fk_ProductId = [p].[ProductId]
WHERE pf.ProductFilterID > @FirstRec AND pf.ProductFilterID < @LastRec
AND Nop_Translate_Product.Language = @Language
ORDER BY p.Name
DROP TABLE #ProductFilter
END