Searching product with sku ?

I have search by SKU for products with more than one SKU (variants/grouped products) on pondliner.com (v3.6.) working perfectly. I accomplished this by modifying the stored procedure dbo.ProductLoadAllPaged. Now we are working on an updated v4.0 test site and that modification no longer works. Does anyone have any ideas on how I can restore this functionality?

Thank you.

preachur wrote:

Could you please post your modification so, I could assist you?

Because I can't assume accurate answare without seen so modification code post it here.

Hope you could understand. !!

sk5202 wrote:

This is the modified query that works in earlier versions.

USE [ponXXXX]
GO
/****** Object:  StoredProcedure [dbo].[ProductLoadAllPaged]    Script Date: 5/16/2018 11:17:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[ProductLoadAllPaged]
(
  @CategoryIds    nvarchar(MAX) = null,  --a list of category IDs (comma-separated list). e.g. 1,2,3
  @ManufacturerId    int = 0,
  @StoreId      int = 0,
  @VendorId      int = 0,
  @WarehouseId    int = 0,
  @ProductTypeId    int = null, --product type identifier, null - load all products
  @VisibleIndividuallyOnly bit = 0,   --0 - load all products , 1 - "visible indivially" only
  @ProductTagId    int = 0,
  @FeaturedProducts  bit = null,  --0 featured only , 1 not featured only, null - load all products
  @PriceMin      decimal(18, 4) = null,
  @PriceMax      decimal(18, 4) = null,
  @Keywords      nvarchar(4000) = null,
  @SearchDescriptions bit = 0, --a value indicating whether to search by a specified "keyword" in product descriptions
  @SearchSku      bit = 0, --a value indicating whether to search by a specified "keyword" in product SKU
  @SearchProductTags  bit = 0, --a value indicating whether to search by a specified "keyword" in product tags
  @UseFullTextSearch  bit = 0,
  @FullTextMode    int = 0, --0 - using CONTAINS with <prefix_term>, 5 - using CONTAINS and OR with <prefix_term>, 10 - using CONTAINS and AND with <prefix_term>
  @FilteredSpecs    nvarchar(MAX) = null,  --filter by attributes (comma-separated list). e.g. 14,15,16
  @LanguageId      int = 0,
  @OrderBy      int = 0, --0 - position, 5 - Name: A to Z, 6 - Name: Z to A, 10 - Price: Low to High, 11 - Price: High to Low, 15 - creation date
  @AllowedCustomerRoleIds  nvarchar(MAX) = null,  --a list of customer role IDs (comma-separated list) for which a product should be shown (if a subjet to ACL)
  @PageIndex      int = 0,
  @PageSize      int = 2147483644,
  @ShowHidden      bit = 0,
  @OverridePublished  bit = null, --null - process "Published" property according to "showHidden" parameter, true - load only "Published" products, false - load only "Unpublished" products
  @LoadFilterableSpecificationAttributeOptionIds bit = 0, --a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)
  @FilterableSpecificationAttributeOptionIds nvarchar(MAX) = null OUTPUT, --the specification attribute option identifiers applied to loaded products (all pages). returned as a comma separated list of identifiers
  @TotalRecords    int = null OUTPUT
)
AS
BEGIN
  
  /* Products that filtered by keywords */
  CREATE TABLE #KeywordProducts
  (
    [ProductId] int NOT NULL
  )

  DECLARE
    @SearchKeywords bit,
    @sql nvarchar(max),
    @sql_orderby nvarchar(max)

  SET NOCOUNT ON
  
  --filter by keywords
  SET @Keywords = isnull(@Keywords, '')
  SET @Keywords = rtrim(ltrim(@Keywords))
  IF ISNULL(@Keywords, '') != ''
  BEGIN
  SET @SearchSku = 1
    SET @SearchKeywords = 1
    
    IF @UseFullTextSearch = 1
    BEGIN
      --remove wrong chars (' ")
      SET @Keywords = REPLACE(@Keywords, '''', '')
      SET @Keywords = REPLACE(@Keywords, '"', '')
      
      --full-text search
      IF @FullTextMode = 0
      BEGIN
        --0 - using CONTAINS with <prefix_term>
        SET @Keywords = ' "' + @Keywords + '*" '
      END
      ELSE
      BEGIN
        --5 - using CONTAINS and OR with <prefix_term>
        --10 - using CONTAINS and AND with <prefix_term>

        --clean multiple spaces
        WHILE CHARINDEX('  ', @Keywords) > 0
          SET @Keywords = REPLACE(@Keywords, '  ', ' ')

        DECLARE @concat_term nvarchar(100)        
        IF @FullTextMode = 5 --5 - using CONTAINS and OR with <prefix_term>
        BEGIN
          SET @concat_term = 'OR'
        END
        IF @FullTextMode = 10 --10 - using CONTAINS and AND with <prefix_term>
        BEGIN
          SET @concat_term = 'AND'
        END

        --now let's build search string
        declare @fulltext_keywords nvarchar(4000)
        set @fulltext_keywords = N''
        declare @index int    
    
        set @index = CHARINDEX(' ', @Keywords, 0)

        -- if index = 0, then only one field was passed
        IF(@index = 0)
          set @fulltext_keywords = ' "' + @Keywords + '*" '
        ELSE
        BEGIN    
          DECLARE @first BIT
          SET  @first = 1      
          WHILE @index > 0
          BEGIN
            IF (@first = 0)
              SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' '
            ELSE
              SET @first = 0

            SET @fulltext_keywords = @fulltext_keywords + '"' + SUBSTRING(@Keywords, 1, @index - 1) + '*"'          
            SET @Keywords = SUBSTRING(@Keywords, @index + 1, LEN(@Keywords) - @index)            
            SET @index = CHARINDEX(' ', @Keywords, 0)
          end
          
          -- add the last field
          IF LEN(@fulltext_keywords) > 0
            SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' ' + '"' + SUBSTRING(@Keywords, 1, LEN(@Keywords)) + '*"'  
        END
        SET @Keywords = @fulltext_keywords
      END
    END
    ELSE
    BEGIN
      --usual search by PATINDEX
      SET @Keywords = '%' + @Keywords + '%'
    END
    --PRINT @Keywords

    --product name
    SET @sql = '
    INSERT INTO #KeywordProducts ([ProductId])
    SELECT p.Id
    FROM Product p with (NOLOCK)
    WHERE '
    IF @UseFullTextSearch = 1
      SET @sql = @sql + 'CONTAINS(p.[Name], @Keywords) '
    ELSE
      SET @sql = @sql + 'PATINDEX(@Keywords, p.[Name]) > 0 '


    --localized product name
    SET @sql = @sql + '
    UNION
    SELECT lp.EntityId
    FROM LocalizedProperty lp with (NOLOCK)
    WHERE
      lp.LocaleKeyGroup = N''Product''
      AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
      AND lp.LocaleKey = N''Name'''
    IF @UseFullTextSearch = 1
      SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
    ELSE
      SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
  

    IF @SearchDescriptions = 1
    BEGIN
      --product short description
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[ShortDescription], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[ShortDescription]) > 0 '


      --product full description
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[FullDescription], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[FullDescription]) > 0 '



      --localized product short description
      SET @sql = @sql + '
      UNION
      SELECT lp.EntityId
      FROM LocalizedProperty lp with (NOLOCK)
      WHERE
        lp.LocaleKeyGroup = N''Product''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''ShortDescription'''
      IF @UseFullTextSearch = 1
        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
      ELSE
        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
        

      --localized product full description
      SET @sql = @sql + '
      UNION
      SELECT lp.EntityId
      FROM LocalizedProperty lp with (NOLOCK)
      WHERE
        lp.LocaleKeyGroup = N''Product''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''FullDescription'''
      IF @UseFullTextSearch = 1
        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
      ELSE
        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
    END

    --SKU
    IF @SearchSku = 1
    BEGIN
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[Sku], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[Sku]) > 0 '
    END

    IF @SearchProductTags = 1
    BEGIN
      --product tag
      SET @sql = @sql + '
      UNION
      SELECT pptm.Product_Id
      FROM Product_ProductTag_Mapping pptm with(NOLOCK) INNER JOIN ProductTag pt with(NOLOCK) ON pt.Id = pptm.ProductTag_Id
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(pt.[Name], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, pt.[Name]) > 0 '

      --localized product tag
      SET @sql = @sql + '
      UNION
      SELECT pptm.Product_Id
      FROM LocalizedProperty lp with (NOLOCK) INNER JOIN Product_ProductTag_Mapping pptm with(NOLOCK) ON lp.EntityId = pptm.ProductTag_Id
      WHERE
        lp.LocaleKeyGroup = N''ProductTag''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''Name'''
      IF @UseFullTextSearch = 1
        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
      ELSE
        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
    END

    --PRINT (@sql)
    EXEC sp_executesql @sql, N'@Keywords nvarchar(4000)', @Keywords

  END
  ELSE
  BEGIN
    SET @SearchKeywords = 0
  END

  --filter by category IDs
  SET @CategoryIds = isnull(@CategoryIds, '')  
  CREATE TABLE #FilteredCategoryIds
  (
    CategoryId int not null
  )
  INSERT INTO #FilteredCategoryIds (CategoryId)
  SELECT CAST(data as int) FROM [nop_splitstring_to_table](@CategoryIds, ',')  
  DECLARE @CategoryIdsCount int  
  SET @CategoryIdsCount = (SELECT COUNT(1) FROM #FilteredCategoryIds)

  --filter by attributes
  SET @FilteredSpecs = isnull(@FilteredSpecs, '')  
  CREATE TABLE #FilteredSpecs
  (
    SpecificationAttributeOptionId int not null
  )
  INSERT INTO #FilteredSpecs (SpecificationAttributeOptionId)
  SELECT CAST(data as int) FROM [nop_splitstring_to_table](@FilteredSpecs, ',')
  DECLARE @SpecAttributesCount int  
  SET @SpecAttributesCount = (SELECT COUNT(1) FROM #FilteredSpecs)

  --filter by customer role IDs (access control list)
  SET @AllowedCustomerRoleIds = isnull(@AllowedCustomerRoleIds, '')  
  CREATE TABLE #FilteredCustomerRoleIds
  (
    CustomerRoleId int not null
  )
  INSERT INTO #FilteredCustomerRoleIds (CustomerRoleId)
  SELECT CAST(data as int) FROM [nop_splitstring_to_table](@AllowedCustomerRoleIds, ',')
  
  --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 #DisplayOrderTmp
  (
    [Id] int IDENTITY (1, 1) NOT NULL,
    [ProductId] int NOT NULL
  )

  SET @sql = '
  INSERT INTO #DisplayOrderTmp ([ProductId])
  SELECT p.Id
  FROM
    Product p with (NOLOCK)'
  
  IF @CategoryIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    LEFT JOIN Product_Category_Mapping pcm with (NOLOCK)
      ON p.Id = pcm.ProductId'
  END
  
  IF @ManufacturerId > 0
  BEGIN
    SET @sql = @sql + '
    LEFT JOIN Product_Manufacturer_Mapping pmm with (NOLOCK)
      ON p.Id = pmm.ProductId'
  END
  
  IF ISNULL(@ProductTagId, 0) != 0
  BEGIN
    SET @sql = @sql + '
    LEFT JOIN Product_ProductTag_Mapping pptm with (NOLOCK)
      ON p.Id = pptm.Product_Id'
  END
  
  --searching by keywords
  IF @SearchKeywords = 1
  BEGIN
    SET @sql = @sql + '
    JOIN #KeywordProducts kp
      ON  p.Id = kp.ProductId'
  END
  
  SET @sql = @sql + '
  WHERE
    p.Deleted = 0'
  
  --filter by category
  IF @CategoryIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    AND pcm.CategoryId IN (SELECT CategoryId FROM #FilteredCategoryIds)'
    
    IF @FeaturedProducts IS NOT NULL
    BEGIN
      SET @sql = @sql + '
    AND pcm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
    END
  END
  
  --filter by manufacturer
  IF @ManufacturerId > 0
  BEGIN
    SET @sql = @sql + '
    AND pmm.ManufacturerId = ' + CAST(@ManufacturerId AS nvarchar(max))
    
    IF @FeaturedProducts IS NOT NULL
    BEGIN
      SET @sql = @sql + '
    AND pmm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
    END
  END
  
  --filter by vendor
  IF @VendorId > 0
  BEGIN
    SET @sql = @sql + '
    AND p.VendorId = ' + CAST(@VendorId AS nvarchar(max))
  END
  
  --filter by warehouse
  IF @WarehouseId > 0
  BEGIN
    --we should also ensure that 'ManageInventoryMethodId' is set to 'ManageStock' (1)
    --but we skip it in order to prevent hard-coded values (e.g. 1) and for better performance
    SET @sql = @sql + '
    AND  
      (
        (p.UseMultipleWarehouses = 0 AND
          p.WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ')
        OR
        (p.UseMultipleWarehouses > 0 AND
          EXISTS (SELECT 1 FROM ProductWarehouseInventory [pwi]
          WHERE [pwi].WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ' AND [pwi].ProductId = p.Id))
      )'
  END
  
  --filter by product type
  IF @ProductTypeId is not null
  BEGIN
    SET @sql = @sql + '
    AND p.ProductTypeId = ' + CAST(@ProductTypeId AS nvarchar(max))
  END
  
  --filter by parent product identifer
  IF @VisibleIndividuallyOnly = 1
  BEGIN
    SET @sql = @sql + '
    AND p.VisibleIndividually = 1'
  END
  
  --filter by product tag
  IF ISNULL(@ProductTagId, 0) != 0
  BEGIN
    SET @sql = @sql + '
    AND pptm.ProductTag_Id = ' + CAST(@ProductTagId AS nvarchar(max))
  END
  
  --"Published" property
  IF (@OverridePublished is null)
  BEGIN
    --process according to "showHidden"
    IF @ShowHidden = 0
    BEGIN
      SET @sql = @sql + '
      AND p.Published = 1'
    END
  END
  ELSE IF (@OverridePublished = 1)
  BEGIN
    --published only
    SET @sql = @sql + '
    AND p.Published = 1'
  END
  ELSE IF (@OverridePublished = 0)
  BEGIN
    --unpublished only
    SET @sql = @sql + '
    AND p.Published = 0'
  END
  
  --show hidden
  IF @ShowHidden = 0
  BEGIN
    SET @sql = @sql + '
    AND p.Deleted = 0
    AND (getutcdate() BETWEEN ISNULL(p.AvailableStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.AvailableEndDateTimeUtc, ''1/1/2999''))'
  END
  
  --min price
  IF @PriceMin is not null
  BEGIN
    SET @sql = @sql + '
    AND (
        (
          --special price (specified price and valid date range)
          (p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.SpecialPrice >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
        )
        OR
        (
          --regular price (price isnt specified or date range isnt valid)
          (p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
        )
      )'
  END
  
  --max price
  IF @PriceMax is not null
  BEGIN
    SET @sql = @sql + '
    AND (
        (
          --special price (specified price and valid date range)
          (p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.SpecialPrice <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
        )
        OR
        (
          --regular price (price isnt specified or date range isnt valid)
          (p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
        )
      )'
  END
  
  --show hidden and ACL
  IF @ShowHidden = 0
  BEGIN
    SET @sql = @sql + '
    AND (p.SubjectToAcl = 0 OR EXISTS (
      SELECT 1 FROM #FilteredCustomerRoleIds [fcr]
      WHERE
        [fcr].CustomerRoleId IN (
          SELECT [acl].CustomerRoleId
          FROM [AclRecord] acl with (NOLOCK)
          WHERE [acl].EntityId = p.Id AND [acl].EntityName = ''Product''
        )
      ))'
  END
  
  --show hidden and filter by store
  IF @StoreId > 0
  BEGIN
    SET @sql = @sql + '
    AND (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = ''Product'' and [sm].StoreId=' + CAST(@StoreId AS nvarchar(max)) + '
      ))'
  END
  
  --filter by specs
  IF @SpecAttributesCount > 0
  BEGIN
    SET @sql = @sql + '
    AND NOT EXISTS (
      SELECT 1 FROM #FilteredSpecs [fs]
      WHERE
        [fs].SpecificationAttributeOptionId NOT IN (
          SELECT psam.SpecificationAttributeOptionId
          FROM Product_SpecificationAttribute_Mapping psam with (NOLOCK)
          WHERE psam.AllowFiltering = 1 AND psam.ProductId = p.Id
        )
      )'
  END
  
  --sorting
  SET @sql_orderby = ''  
  IF @OrderBy = 5 /* Name: A to Z */
    SET @sql_orderby = ' p.[Name] ASC'
  ELSE IF @OrderBy = 6 /* Name: Z to A */
    SET @sql_orderby = ' p.[Name] DESC'
  ELSE IF @OrderBy = 10 /* Price: Low to High */
    SET @sql_orderby = ' p.[Price] ASC'
  ELSE IF @OrderBy = 11 /* Price: High to Low */
    SET @sql_orderby = ' p.[Price] DESC'
  ELSE IF @OrderBy = 15 /* creation date */
    SET @sql_orderby = ' p.[CreatedOnUtc] DESC'
  ELSE /* default sorting, 0 (position) */
  BEGIN
    --category position (display order)
    IF @CategoryIdsCount > 0 SET @sql_orderby = ' pcm.DisplayOrder ASC'
    
    --manufacturer position (display order)
    IF @ManufacturerId > 0
    BEGIN
      IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
      SET @sql_orderby = @sql_orderby + ' pmm.DisplayOrder ASC'
    END
    
    --name
    IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
    SET @sql_orderby = @sql_orderby + ' p.[Name] ASC'
  END
  
  SET @sql = @sql + '
  ORDER BY' + @sql_orderby
  
  --PRINT (@sql)
  EXEC sp_executesql @sql

  DROP TABLE #FilteredCategoryIds
  DROP TABLE #FilteredSpecs
  DROP TABLE #FilteredCustomerRoleIds
  DROP TABLE #KeywordProducts

  CREATE TABLE #PageIndex
  (
    [IndexId] int IDENTITY (1, 1) NOT NULL,
    [ProductId] int NOT NULL
  )
  INSERT INTO #PageIndex ([ProductId])
  SELECT ProductId
  FROM #DisplayOrderTmp
  GROUP BY ProductId
  ORDER BY min([Id])

  --total records
  SET @TotalRecords = @@rowcount
  
  DROP TABLE #DisplayOrderTmp

  --prepare filterable specification attribute option identifier (if requested)
  IF @LoadFilterableSpecificationAttributeOptionIds = 1
  BEGIN    
    CREATE TABLE #FilterableSpecs
    (
      [SpecificationAttributeOptionId] int NOT NULL
    )
    INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
    SELECT DISTINCT [psam].SpecificationAttributeOptionId
    FROM [Product_SpecificationAttribute_Mapping] [psam] with (NOLOCK)
    WHERE [psam].[AllowFiltering] = 1
    AND [psam].[ProductId] IN (SELECT [pi].ProductId FROM #PageIndex [pi])

    --build comma separated list of filterable identifiers
    SELECT @FilterableSpecificationAttributeOptionIds = COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '') + CAST(SpecificationAttributeOptionId as nvarchar(4000))
    FROM #FilterableSpecs

    DROP TABLE #FilterableSpecs
  END

  --return products
  SELECT TOP (@RowsToReturn)
    p.*
  FROM
    #PageIndex [pi]
    INNER JOIN Product p with (NOLOCK) on p.Id = [pi].[ProductId]
  WHERE
    [pi].IndexId > @PageLowerBound AND
    [pi].IndexId < @PageUpperBound
  ORDER BY
    [pi].IndexId
  
  DROP TABLE #PageIndex
END

So is that it then? The search got worse, and now there's no fix for it???

Is there any update on this for nopCommerce 4?  I don't see searchSku in CatalogController.cs.  I do see it in ProductService.cs.  It is set to true in there, but SKU searches don't work on our site.  Is there something else to try?

Thanks!

Jeremy

RE:  "...SKU searches don't work on our site"

In the public store, or in Admin > Products >> "Go directly to product SKU"?

(what version of "4"- 4.00? 4.10? 4.20?)

In the public store.  It works fine to go directly to SKU in the admin.  We're on 4.0.

Thanks!

Jeremy

In 4.20 SKUs aren't searched in ProductAttributeCombinations.  I added the following into the LoadAllPaged stored procedure and it works fine (MPU matters to us but you dont need it)

    IF @SearchSku = 1
    BEGIN
      SET @sql = @sql + '
      UNION
      SELECT p.Id
        FROM Product p with (NOLOCK) LEFT OUTER JOIN ProductAttributeCombination pac ON (p.Id = pac.ProductId)
        WHERE
        (p.[Sku] = @OriginalKeywords
        OR p.ManufacturerPartNumber = @OriginalKeywords
        OR pac.[Sku] = @OriginalKeywords
        OR pac.ManufacturerPartNumber = @OriginalKeywords) '
    END

tonyduckett wrote:

Thanks for sharing this.Very useful. +1

An update for my earlier post - I have amended the SKU search to work with LIKE based on  user feedback - it now reads as:

    IF @SearchSku = 1
    BEGIN
      SET @sql = @sql + '
      UNION
      SELECT p.Id
        FROM Product p with (NOLOCK) LEFT OUTER JOIN ProductAttributeCombination pac ON (p.Id = pac.ProductId)
        WHERE
        (p.[Sku] like ''%''+''' + @OriginalKeywords + '''+''%''  
        OR p.ManufacturerPartNumber = @OriginalKeywords
        OR pac.[Sku] like ''%''+''' + @OriginalKeywords + '''+''%''  
        OR pac.ManufacturerPartNumber = @OriginalKeywords) '
    END