SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 DECLARE @categoryId INT
 DECLARE @keywords NVARCHAR (MAX)
 DECLARE @ProductsPerPage INT
 DECLARE @rowIndex INT=0
 DECLARE @OrderBy NVARCHAR (100)
 DECLARE @ASC BIT=1
 DECLARE @showOutofStock BIT
 DECLARE @TotalProducts INT

 --SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @KeywordMatchedProducts NVARCHAR(MAX)

 SET @KeywordMatchedProducts = ''
 SET @keywords = 'smartreader 7'
 SET @ProductsPerPage = 24
 SET @OrderBy = 'productName'
 SET @categoryId =NULL
 SET  @showOutofStock =NULL
 IF ((@keywords IS NOT NULL) AND (LEN(@keywords) > 0))
  BEGIN
   SELECT @KeywordMatchedProducts = @KeywordMatchedProducts + '
    (SELECT  p.ProductId
    FROM products productsToCheck
    JOIN dbo.DSC_Product p
      ON productsToCheck.productId = p.productId
    WHERE   CONTAINS(p.*,' + QUOTENAME('FORMSOF(INFLECTIONAL,' + QUOTENAME(dbo.DSC_Escape(dbo.DSC_Escape(Value, N'\'), N';'), '"') + ')', '''') +  ')
    UNION
    SELECT  p.productId
    FROM products p
    JOIN DSC_Product_Descriptor pd
      ON pd.productId = p.productId
    WHERE CONTAINS(pd.*,' + QUOTENAME('FORMSOF(INFLECTIONAL,' + QUOTENAME(dbo.DSC_Escape(dbo.DSC_Escape(Value, N'\'), N';'), '"') + ')', '''') +  ')
    UNION
    SELECT  p.productId
    FROM products p
    JOIN DSC_Product_Attribute_Map pa
      ON pa.productId = p.productId
    JOIN DSC_Attribute a
      ON a.attributeId = pa.attributeId
    WHERE CONTAINS(a.*,' + QUOTENAME('FORMSOF(INFLECTIONAL,' + QUOTENAME(dbo.DSC_Escape(dbo.DSC_Escape(Value, N'\'), N';'), '"') + ')', '''') +  ')
    UNION
    SELECT  p.productId
    FROM products p
    JOIN DSC_Product_Attribute_Map pa
      ON pa.productId = p.productId
    JOIN DSC_Product_Attribute_Value pav
      ON pav.productAttributeId = pa.productAttributeId
    WHERE CONTAINS(pav.*,' + QUOTENAME('FORMSOF(INFLECTIONAL,' + QUOTENAME(dbo.DSC_Escape(dbo.DSC_Escape(Value, N'\'), N';'), '"') + ')', '''') +  ')
    UNION
    SELECT  p.productId
    FROM products p
    JOIN dbo.DSC_Manufacturer m
      ON m.manufacturerId = p.manufacturerId
    WHERE CONTAINS(m.*,' + QUOTENAME('FORMSOF(INFLECTIONAL,' + QUOTENAME(dbo.DSC_Escape(dbo.DSC_Escape(Value, N'\'), N';'), '"') + ')', '''') +  '))
    INTERSECT'
   FROM DSC_Parse_Values(@keywords, DEFAULT)
   SET  @KeywordMatchedProducts = LEFT(@KeywordMatchedProducts, LEN(@KeywordMatchedProducts) - 9) -- remove tailing INTERSECT
  END
 ELSE
  SET @KeywordMatchedProducts = 'SELECT productId FROM products'
 
 DECLARE @ASC_DESC NVARCHAR(10)
 IF (@ASC = 1)
  SET @ASC_DESC = ''
 ELSE
  SET @ASC_DESC = ' DESC'

 CREATE TABLE #product (RowNumber INT, productId INT)

 DECLARE @searchStatement NVARCHAR(MAX)
 SET  @searchStatement = '
 ;WITH  products AS
 (
  SELECT p.productId, p.manufacturerId
  FROM dbo.DSC_Product p '

 IF (@categoryId IS NOT NULL)
 BEGIN
  SET @searchStatement = @searchStatement +
  'JOIN DSC_Category_Product_Map cpmap
    ON cpmap.productId = p.productId
  JOIN DSC_Category_GetChildren(' + CAST(@categoryId AS VARCHAR(20)) + ') c
    ON c.categoryId = cpmap.categoryId '
 END

 SET @searchStatement = @searchStatement +
 '),
 keywordMatchedProducts AS
 (' + @KeywordMatchedProducts + '
 ),
 matchedProducts AS
 (
  SELECT DISTINCT productId
  FROM keywordMatchedProducts
 )
 INSERT INTO #product
 SELECT ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@OrderBy) + @ASC_DESC + ') AS RowNumber, mp.productId
 FROM matchedProducts mp
 JOIN DSC_Product p
   ON p.productId = mp.productId
 JOIN vw_DSC_Product_Stock ps
   ON ps.productId = p.productId
 WHERE ((@showOutofStock IS NULL) OR (((ps.InStock = 1) OR (p.showIfOutofStock = 1) OR ((p.showIfOutofStock IS NULL) AND (@showOutofStock = 1))) AND (p.statusId < 200)))'

 PRINT @searchStatement
 EXECUTE sp_executesql @searchStatement, N'@showOutofStock BIT', @showOutofStock
/*
 SELECT @TotalProducts = COUNT(*)
 FROM #product
*/
 SELECT products.RowNumber, products.productId
 INTO #subset_Product
 FROM #product products
 WHERE products.RowNumber BETWEEN @rowIndex + 1 AND @rowIndex + @ProductsPerPage
 ORDER BY products.RowNumber
 
 DROP TABLE #product
 
 SELECT products.*, p.* -- need to sort by RowNumber if the column exist
 FROM #subset_Product products
 JOIN DSC_Product p
   ON products.productId = p.productId
 ORDER BY 1 
 --EXEC DSC_Product_GetSummaryByTempTable
    DROP TABLE #subset_Product