Search match

USE [demostore]
GO
/****** Object:  StoredProcedure [demostore1].[usp_SearchProducts]    Script Date: 08/05/2009 14:13:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
  
ALTER PROCEDURE [demostore1].[usp_SearchProducts]
(
      @PageSize FLOAT,
      @TargetPage SMALLINT,
      @Highest Bit = 0,      
      @Sto VARCHAR(5),
      @Dept INT = NULL, 
      @catID INT = NULL,
      @keyword VARCHAR(50),
      @FromPrice MONEY,
      @ToPrice MONEY,
      @onSale Bit = 0
 
)
AS
WITH Sales_CTE(PageNumber, CatID, CatName, CatPos, DepID, DepName, depKey, DepDes, storeID, storeName, storeKey, storeDes,ProdID,
            ProductName, ProductCode, Price, SalePrice, RetailPrice, PageName, PageDescription, PageDescription2, Active, Extra1, Extra2, tax)
      AS
      (     
                  
            SELECT
            CEILING((ROW_NUMBER() OVER (ORDER BY c.CatID ASC))/@PageSize) AS PageNumber, 
            c.catId, c.name as catName, c.pos as catPos, d.depId, d.name as depName, d.metaKeywords as depKey, d.metaDescription as depDes, s.storeId, s.name as storeName, s.metaKeywords as storeKey, s.metaDescription as storeDes, p.prodId, p.name, p.productCode, p.price, p.salePrice, p.retailPrice, p.pageName, p.description, p.description2, p.active, p.extra1, p.EXTRA2, p.tax
            FROM departments d
            INNER JOIN stores s on s.storeId = d.storeId
            INNER JOIN categories c on c.depId = d.depId
            INNER JOIN Products p on c.catid = p.catId
            WHERE d.storeId = @Sto AND p.active = 'on' 
            AND p.name LIKE '%' + @keyword + '%'
            OR  P.description LIKE '%' + @keyword + '%'
            AND (p.Price Between @FromPrice And @ToPrice Or p.SalePrice Between @FromPrice And @ToPrice) 
	        AND (c.depId = @Dept or @dept = 0)
            AND (@onSale = 0 or p.SalePrice > 0 )
  
    
 
 
 
            
      )
      SELECT s.*
           , ( SELECT COUNT(*) FROM Sales_CTE) / @PageSize as recCount, (SELECT COUNT(*) FROM Sales_CTE) as totCount 
        FROM Sales_CTE s
      WHERE  PageNumber = @Targetpage
      ORDER BY CASE WHEN @Highest = 1 THEN price else 0 END DESC 
          , CASE WHEN @Highest = 0 THEN price else 0 END ASC