Thread: Sorting Items

Maybe Im missing something, im not sure.

In the string resource manager, the strings "sort", "low to high", "high to low", "sort by:"

Are these actually used somewhere, or were they a developers cruel joke saying yea, you could sort the items, but thats your problem, but we'll make pretty little strings for them anyway ??

If they are built in, how by chance would you call them ?

this is also listed in the products matrix...

C#/VB.NET Code:

Sort products in any order desired (you can force popular products to the top of the pages) 


any help would be appreciated

Xoticrox,

What exactly do you want to sort? Is it a search result? Or a category listing? A manufacturer's listing?

I never thought about this sorting before, as for my products most of the time sorting is not useful (apart from the built-in display order), but now that you mentioned it, maybe it can be interesting to implement for some situations.

fsantos

i would like for the customer to be able to sort items on any given page by the price eof the item (ascending or decending), or by the name of the item (ascending or decending).

I would like to add that to any page that has products on it.

Also, and I imagine that if I knew how to accomplish the above, I would like the ability to sort the items on the search reasults page. ( I was able to add a column on there for price so that the price now shows in the search results. )

We cannot add these items instantly, but yes, they are in discussion and in some cases in progress....

Hi,

Any idea of what ever happened with this? In March a staffer said it was under consideration, but I see it's still not implemented.

Any updates?

I agree, sorting the products by price should 100% be a standard feature.

I have no idea how to set it up and don't want to pay someone a ton of money to code it into our store.

C'mon guys, even the free carts have sorting by price built in as a standard feature. You would be surprised how many of your customers would love to have this, and it would probably take one of your techs a couple hours to code it in.

Please provide us sorting

RE: price sorting...we've indicated a number of times, that's not quite so simple as everyone thinks all the time.

Which price? regular, sale, customer level (extended?) what if the related product is a kit, they don't have prices until configured? What if the product has sizes & colors with price modifiers, again, you won't konw the price until they select the attributes they want. if currency conversions are enabled, have to do that also. What about VAT inc/ex...What if they have a product coupon, should we factor that into the sorting? What about quantity discounts applied to pricing?

Everyone thinks that "showing a price" is dirt simple and 2 mins work, but it's actually quite complicated...UNLESS you know for your own store, that you don't care about any of the complications mentioned above and then you can just dump a price out to the page.

We cannot make such simple assumptions if we put it in the core.

I'm not saying we can't, or won't add it, but most of the time it's VASTLY easier to just do it for your storefront where you know the specific rules. I'll talk to dev on this again to see what makes sense.

Thanks for replying so quick. I completely agree that if you were to take all the things you mentioned above into account it would be a huge piece of code to write.

However, if you were to take a poll from the customer base you would see the majority of people would be happy with the simple regular price sorting.

Anyone that needs to worry about customer levels, vats, kits and currency all at the same time will most likely have a customized setup anyway.

Dont think I'm raggin' on you guys, I love the cart. In my opinion it's the best one on the market and I wouldn't trade it for anything. Just tryin' to give a suggestion that I'm sure many people would really appreciate.

Here is how I did it if anybody would like to incorporate sort by price.
1. run the following SQL:

C#/VB.NET Code:

USE [AspDotNetStorefront]
GO
/****** Object:  StoredProcedure [dbo].[aspdnsf_GetProducts]    Script Date: 12/31/2007 10:15:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[aspdnsf_GetProducts]
    @categoryID      int = null,
    @sectionID       int = null,
    @manufacturerID  int = null,
    @distributorID   int = null,
    @genreID         int = null,
    @vectorID        int = null,
    @localeID        int = null,
    @CustomerLevelID int = null,
    @affiliateID     int = null,
    @ProductTypeID   int = null,
    @ViewType        bit = 1, -- 0 = all variants, 1 = one variant
    @sortEntity      int = 0, -- 1 = category, 2 = section, 3 = manufacturer, 4 = distributor, 5= genre, 6 = vector
    @pagenum         int = 1,
    @pagesize        int = null,
    @StatsFirst      tinyint = 1,
    @searchstr       nvarchar(4000) = null,
    @extSearch       tinyint = 0,
    @publishedonly   tinyint = 0,
    @ExcludePacks    tinyint = 0,
    @ExcludeKits     tinyint = 0,
    @ExcludeSysProds tinyint = 0,
    @InventoryFilter int = 0,  --  will only show products with an inventory level GREATER OR EQUAL TO than the number specified in this parameter, set to -1 to disable inventory filtering
    @sortEntityName  varchar(20) = '', -- usely only when the entity id is provided, allowed values: category, section, manufacturer, distributor, genre, vector
    @localeName      varchar(20) = '',
    @OnSaleOnly      tinyint = 0
AS
BEGIN

    SET NOCOUNT ON 

    DECLARE @rcount int
    DECLARE @productfilter table (rownum int not null identity  primary key, productid int not null, displayorder int not null, VariantID int not null, VariantDisplayOrder int not null, ProductName nvarchar(400) null, VariantName nvarchar(400) null)
    DECLARE @FilterProductsByAffiliate tinyint, @FilterProductsByCustomerLevel tinyint, @HideProductsWithLessThanThisInventoryLevel int
    --Temp table I create to give me the row number of results I sort how I want.
    CREATE TABLE #RowNumber (displayorder int IDENTITY(1,1), productid int, categoryID int)
    CREATE TABLE #displayorder (productid int not null primary key, displayorder int not null)
    CREATE TABLE #inventoryfilter (productid int not null, variantid int not null, InvQty int not null)
    CREATE CLUSTERED INDEX tmp_inventoryfilter ON #inventoryfilter (productid, variantid)

    DECLARE @custlevelcount int, @sectioncount int, @localecount int, @affiliatecount int, @categorycount int, @CustomerLevelFilteringIsAscending bit, @distributorcount int, @genrecount int, @vectorcount int, @manufacturercount int

    SELECT @FilterProductsByAffiliate = case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig WHERE [Name] = 'FilterProductsByAffiliate'
    SELECT @FilterProductsByCustomerLevel = case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig WHERE [Name] = 'FilterProductsByCustomerLevel'
    SELECT @HideProductsWithLessThanThisInventoryLevel = convert(int, ConfigValue) FROM dbo.AppConfig WHERE [Name] = 'HideProductsWithLessThanThisInventoryLevel' and isnumeric(ConfigValue) = 1

    IF @InventoryFilter <> -1 and (@HideProductsWithLessThanThisInventoryLevel > @InventoryFilter or @HideProductsWithLessThanThisInventoryLevel  = -1)
        SET @InventoryFilter  = @HideProductsWithLessThanThisInventoryLevel


    SET @categoryID      = nullif(@categoryID, 0)
    SET @sectionID       = nullif(@sectionID, 0)
    SET @manufacturerID  = nullif(@manufacturerID, 0)
    SET @distributorID   = nullif(@distributorID, 0)
    SET @genreID            = nullif(@genreID, 0)
    SET @vectorID        = nullif(@vectorID, 0)
    SET @affiliateID     = nullif(@affiliateID, 0)
    SET @ProductTypeID   = nullif(@ProductTypeID, 0)


    SET @CustomerLevelFilteringIsAscending  = 0
    SELECT @CustomerLevelFilteringIsAscending  = case configvalue when 'true' then 1 else 0 end
    FROM dbo.AppConfig
    WHERE name = 'FilterByCustomerLevelIsAscending'

    IF @localeID is null and ltrim(rtrim(@localeName)) <> ''
        SELECT @localeID = LocaleSettingID FROM LocaleSetting WHERE Name = ltrim(rtrim(@localeName))

    select @categorycount     = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('productcategory') and si.indid < 2 and type = 'u'
    select @sectioncount      = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('productsection') and si.indid < 2 and type = 'u'
    select @localecount       = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductLocaleSetting') and si.indid < 2 and type = 'u'
    select @custlevelcount    = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductCustomerLevel') and si.indid < 2 and type = 'u'
    select @affiliatecount    = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductAffiliate') and si.indid < 2 and type = 'u'
    select @distributorcount  = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductDistributor') and si.indid < 2 and type = 'u'
    select @genrecount        = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductGenre') and si.indid < 2 and type = 'u'
    select @vectorcount       = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductVector') and si.indid < 2 and type = 'u'
    select @manufacturercount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductManufacturer') and si.indid < 2 and type = 'u'


    -- get page size
    IF @pagesize is null or @pagesize = 0 BEGIN
        IF @categoryID is not null
            SELECT @pagesize = PageSize FROM dbo.Category WHERE categoryID = @categoryID
        ELSE IF @sectionID is not null
            SELECT @pagesize = PageSize FROM dbo.Section WHERE sectionID = @sectionID
        ELSE IF @manufacturerID is not null
            SELECT @pagesize = PageSize FROM dbo.Manufacturer WHERE manufacturerID = @manufacturerID
        ELSE IF @distributorID is not null
            SELECT @pagesize = PageSize FROM dbo.Distributor WHERE distributorID = @distributorID
        ELSE IF @genreID is not null
            SELECT @pagesize = PageSize FROM dbo.Genre WHERE genreID = @genreID
        ELSE IF @vectorID is not null
            SELECT @pagesize = PageSize FROM dbo.Vector WHERE vectorID = @vectorID
        ELSE 
            SELECT @pagesize = convert(int, ConfigValue) FROM dbo.AppConfig WHERE [Name] = 'Default_CategoryPageSize'
    END

    IF @pagesize is null or @pagesize = 0
        SET @pagesize = 20

    -- get sort order
    IF @sortEntity = 1 or @sortEntityName = 'category' BEGIN
        INSERT #displayorder select productid, displayorder from ProductCategory where categoryID = @categoryID
    END
    ELSE IF @sortEntity = 2 or @sortEntityName = 'section' BEGIN
        INSERT #displayorder select productid, displayorder from ProductSection where sectionId = @sectionID
    END
    ELSE IF @sortEntity = 3 or @sortEntityName = 'manufacturer' BEGIN
        INSERT #displayorder select productid, displayorder from ProductManufacturer where ManufacturerID = @manufacturerID
    END
    ELSE IF @sortEntity = 4 or @sortEntityName = 'distributor' BEGIN
        INSERT #displayorder select productid, displayorder from ProductDistributor where DistributorID = @distributorID
    END
    ELSE IF @sortEntity = 5 or @sortEntityName = 'genre' BEGIN
        INSERT #displayorder select productid, displayorder from ProductGenre where GenreID = @genreID
    END
    ELSE IF @sortEntity = 6 or @sortEntityName = 'vector' BEGIN
        INSERT #displayorder select productid, displayorder from ProductVector where VectorID = @vectorID
    END
    ELSE IF @sortEntity = 7 or @sortEntityName = 'categoryprice' BEGIN

        INSERT #RowNumber (productID, categoryID)
        SELECT PC.Productid, PC.CategoryID
        FROM ProductCategory PC INNER JOIN ProductVariant PV
            ON PC.productID = PV.productID
        WHERE PC.CategoryID = @categoryID
        GROUP BY PC.ProductID, PC.CategoryID
        ORDER BY MIN(PV.Price)

        INSERT #displayorder select productid, displayorder from #RowNumber where categoryID = @categoryID
    END
    ELSE IF @sortEntity = 8 or @sortEntityName = 'sectionprice' BEGIN

        INSERT #RowNumber (productID, categoryID)
        SELECT PS.Productid, PS.SectionID
        FROM ProductSection PS INNER JOIN ProductVariant PV
            ON PS.productID = PV.productID
        WHERE PS.SectionID = @sectionID
        GROUP BY PS.ProductID, PS.SectionID
        ORDER BY MIN(PV.Price)

        INSERT #displayorder select productid, displayorder from #RowNumber where categoryID = @sectionID
    END
    ELSE IF @sortEntity = 9 or @sortEntityName = 'manufacturerprice' BEGIN

        INSERT #RowNumber (productID, categoryID)
        SELECT PM.Productid, PM.ManufacturerID
        FROM ProductManufacturer PM INNER JOIN ProductVariant PV
            ON PM.productID = PV.productID
        WHERE PM.ManufacturerID = @ManufacturerID
        GROUP BY PM.ProductID, PM.ManufacturerID
        ORDER BY MIN(PV.Price)

        INSERT #displayorder select productid, displayorder from #RowNumber where categoryID = @ManufacturerID
    END
    ELSE BEGIN
        INSERT #displayorder select productid, 1 from Product ORDER BY Name
    END



    SET @searchstr = '%' + rtrim(ltrim(@searchstr)) + '%'

    IF @InventoryFilter <> -1 BEGIN

        IF @ViewType = 1 BEGIN
            INSERT #inventoryfilter
            SELECT p.productid, pv.VariantID, sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) invqty
            FROM product p with (NOLOCK) join #displayorder d on p.ProductID = d.ProductID
                join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID  and pv.IsDefault = 1
                left join Inventory i with (NOLOCK) on pv.VariantID = i.VariantID
            GROUP BY p.productid, pv.VariantID
            HAVING sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) >= @InventoryFilter
        END
        ELSE
            INSERT #inventoryfilter
            SELECT p.productid, pv.VariantID, sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) invqty
            FROM product p with (NOLOCK) join #displayorder d on p.ProductID = d.ProductID
                join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID
                left join Inventory i with (NOLOCK) on pv.VariantID = i.VariantID
            GROUP BY p.productid, pv.VariantID
            HAVING sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) >= @InventoryFilter


        INSERT @productfilter (productid, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName)
        SELECT distinct p.productid, do.displayorder, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name
        FROM 
            product p with (nolock)
            join #displayorder do on p.ProductID = do.ProductID 
            left join ProductVariant pv        with (NOLOCK) ON p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
            left join productcategory pc       with (nolock) on p.ProductID = pc.ProductID 
            left join productsection ps        with (nolock) on p.ProductID = ps.ProductID 
            left join ProductManufacturer pm   with (nolock) on p.ProductID = pm.ProductID 
            left join ProductDistributor pd    with (nolock) on p.ProductID = pd.ProductID 
            left join ProductGenre px          with (nolock) on p.ProductID = px.ProductID 
            left join ProductVector px2        with (nolock) on p.ProductID = px2.ProductID 
            left join ProductLocaleSetting pl  with (nolock) on p.ProductID = pl.ProductID 
            left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID 
            left join ProductAffiliate pa      with (nolock) on p.ProductID = pa.ProductID 
            join #inventoryfilter i on pv.VariantID = i.VariantID
        WHERE 
              (pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)
          and (ps.sectionid = @sectionID or @sectionID is null or @sectioncount = 0)
          and (pl.LocaleSettingID = @localeID or @localeID is null or @localecount = 0)
          and (pa.AffiliateID = @affiliateID or pa.AffiliateID is null or @affiliatecount = 0 or @FilterProductsByAffiliate = 0)
          and (pm.manufacturerid = @manufacturerID or @manufacturerID is null or @manufacturercount = 0)
          and (pd.DistributorID = @distributorID or @distributorID is null or @distributorcount = 0)
          and (px.GenreID = @genreID or @genreID is null or @genrecount = 0)
          and (px2.VectorID = @vectorID or @vectorID is null or @vectorcount = 0)
          and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)
          and (case 
                when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null then 1
                when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1 
                when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1 
                else 0
               end  = 1
              )
          and (
                 @searchstr is null
              or patindex(@searchstr, isnull(p.name, '')) > 0
              or patindex(@searchstr, isnull(convert(nvarchar(20),p.productid), '')) > 0 
              or patindex(@searchstr, isnull(pv.name, '')) > 0
              or patindex(@searchstr, isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0
              or patindex(@searchstr, isnull(p.manufacturerpartnumber, '')) > 0
              or patindex(@searchstr, isnull(pv.manufacturerpartnumber, '')) > 0
              or (patindex(@searchstr, isnull(p.Description, '')) > 0 and @extSearch = 1)
              or (patindex(@searchstr, isnull(p.Summary, '')) > 0 and @extSearch = 1)
              )
          and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly
          and p.published >= @publishedonly
          and pv.published >= @publishedonly
          and isnull(p.IsAPack, 0) <= 1-@ExcludePacks
          and isnull(p.IsAKit, 0) <= 1-@ExcludeKits
          and p.IsSystem <= 1-@ExcludeSysProds
          and p.Deleted = 0
          and pv.Deleted = 0
          and getdate() between isnull(p.AvailableStartDate, '1/1/1900') and isnull(p.AvailableStopDate, '1/1/2999')
        order by do.displayorder, p.Name, pv.DisplayOrder, pv.Name

    END 
    ELSE BEGIN
        INSERT @productfilter (productid, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName)
        SELECT distinct p.productid, do.displayorder, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name
        FROM 
            product p with (nolock)
            join #displayorder do on p.ProductID = do.ProductID 
            join ProductVariant pv             with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
            left join productcategory pc       with (nolock) on p.ProductID = pc.ProductID 
            left join productsection ps        with (nolock) on p.ProductID = ps.ProductID 
            left join ProductManufacturer pm   with (nolock) on p.ProductID = pm.ProductID 
            left join ProductDistributor pd    with (nolock) on p.ProductID = pd.ProductID 
            left join ProductGenre px          with (nolock) on p.ProductID = px.ProductID 
            left join ProductVector px2        with (nolock) on p.ProductID = px2.ProductID 
            left join ProductLocaleSetting pl  with (nolock) on p.ProductID = pl.ProductID 
            left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID 
            left join ProductAffiliate pa      with (nolock) on p.ProductID = pa.ProductID 
        WHERE 
              (pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)
          and (ps.sectionid = @sectionID or @sectionID is null or @sectioncount = 0)
          and (pl.LocaleSettingID = @localeID or @localeID is null or @localecount = 0)
          and (pa.AffiliateID = @affiliateID or pa.AffiliateID is null or @affiliatecount = 0 or @FilterProductsByAffiliate = 0)
          and (pm.manufacturerid = @manufacturerID or @manufacturerID is null or @manufacturercount = 0)
          and (pd.DistributorID = @distributorID or @distributorID is null or @distributorcount = 0)
          and (px.GenreID = @genreID or @genreID is null or @genrecount = 0)
          and (px2.VectorID = @vectorID or @vectorID is null or @vectorcount = 0)
          and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)
          and (case 
                when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null then 1
                when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1 
                when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1 
                else 0
               end  = 1
              )
          and (
                 @searchstr is null
              or patindex(@searchstr, isnull(p.name, '')) > 0
              or patindex(@searchstr, isnull(convert(nvarchar(20),p.productid), '')) > 0 
              or patindex(@searchstr, isnull(pv.name, '')) > 0
              or patindex(@searchstr, isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0
              or patindex(@searchstr, isnull(p.manufacturerpartnumber, '')) > 0
              or patindex(@searchstr, isnull(pv.manufacturerpartnumber, '')) > 0
              or (patindex(@searchstr, isnull(p.Description, '')) > 0 and @extSearch = 1)
              or (patindex(@searchstr, isnull(p.Summary, '')) > 0 and @extSearch = 1)
              )
          and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly
          and p.published >= @publishedonly
          and pv.published >= @publishedonly
          and isnull(p.IsAPack, 0) <= 1-@ExcludePacks
          and isnull(p.IsAKit, 0) <= 1-@ExcludeKits
          and p.IsSystem <= 1-@ExcludeSysProds
          and p.Deleted = 0
          and pv.Deleted = 0
          and getdate() between isnull(p.AvailableStartDate, '1/1/1900') and isnull(p.AvailableStopDate, '1/1/2999')
        order by do.displayorder, p.Name, pv.DisplayOrder, pv.Name

    END

    SET @rcount = @@rowcount
    IF @StatsFirst = 1
        SELECT cast(ceiling(@rcount*1.0/@pagesize) as int) pages, @rcount ProductCount

    SELECT 
        p.ProductID,
        p.Name,
        pv.VariantID,
        pv.Name AS VariantName,
        p.ProductGUID,
        p.Summary,
        p.Description,
        p.SEKeywords,
        p.SEDescription,
        p.SpecTitle,
        p.MiscText,
        p.SwatchImageMap,
        p.IsFeaturedTeaser,
        p.FroogleDescription,
        p.SETitle,
        p.SENoScript,
        p.SEAltText,
        p.SizeOptionPrompt,
        p.ColorOptionPrompt,
        p.TextOptionPrompt,
        p.ProductTypeID,
        p.TaxClassID,
        p.SKU,
        p.ManufacturerPartNumber,
        p.SalesPromptID,
        p.SpecCall,
        p.SpecsInline,
        p.IsFeatured,
        p.XmlPackage,
        p.ColWidth,
        p.Published,
        p.RequiresRegistration,
        p.Looks,
        p.Notes,
        p.QuantityDiscountID,
        p.RelatedProducts,
        p.UpsellProducts,
        p.UpsellProductDiscountPercentage,
        p.RelatedDocuments,
        p.TrackInventoryBySizeAndColor,
        p.TrackInventoryBySize,
        p.TrackInventoryByColor,
        p.IsAKit,
        p.ShowInProductBrowser,
        p.IsAPack,
        p.PackSize,
        p.ShowBuyButton,
        p.RequiresProducts,
        p.HidePriceUntilCart,
        p.IsCalltoOrder,
        p.ExcludeFromPriceFeeds,
        p.RequiresTextOption,
        p.TextOptionMaxLength,
        p.SEName,
        p.Deleted,
        p.CreatedOn,
        p.ImageFileNameOverride,
        pv.VariantGUID,
        pv.Description AS VariantDescription,
        pv.SEKeywords AS VariantSEKeywords,
        pv.SEDescription AS VariantSEDescription,
        pv.Colors,
        pv.ColorSKUModifiers,
        pv.Sizes,
        pv.SizeSKUModifiers,
        pv.FroogleDescription AS VariantFroogleDescription,
        pv.SKUSuffix,
        pv.ManufacturerPartNumber AS VariantManufacturerPartNumber,
        pv.Price,
        pv.CustomerEntersPrice, 
        pv.CustomerEntersPricePrompt,
        isnull(pv.SalePrice, 0) SalePrice,
        cast(isnull(pv.Weight,0) as decimal(10,1)) Weight,
        pv.MSRP,
        pv.Cost,
        isnull(pv.Points,0) Points,
        pv.Dimensions,
        case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end Inventory,
        pv.DisplayOrder as VariantDisplayOrder,
        pv.Notes AS VariantNotes,
        pv.IsTaxable,
        pv.IsShipSeparately,
        pv.IsDownload,
        pv.DownloadLocation,
        pv.Published AS VariantPublished,
        pv.IsSecureAttachment,
        pv.IsRecurring,
        pv.RecurringInterval,
        pv.RecurringIntervalType,
        pv.SubscriptionInterval,
        pv.SEName AS VariantSEName,
        pv.RestrictedQuantities,
        pv.MinimumQuantity,
        pv.Deleted AS VariantDeleted,
        pv.CreatedOn AS VariantCreatedOn,
        d.Name AS DistributorName,
        d.DistributorID,
        d.SEName AS DistributorSEName,
        m.ManufacturerID,
        m.Name AS ManufacturerName,
        m.SEName AS ManufacturerSEName,
        s.Name AS SalesPromptName,
        case when pcl.productid is null then 0 else isnull(ep.Price, 0) end ExtendedPrice
    FROM Product p with (NOLOCK) 
        left join ProductVariant       pv  with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType
        join @productfilter            pf                on pv.ProductID = pf.ProductID and pv.VariantID = pf.VariantID 
        left join SalesPrompt           s  with (NOLOCK) on p.SalesPromptID = s.SalesPromptID 
        left join ProductManufacturer  pm  with (NOLOCK) on p.ProductID = pm.ProductID 
        left join Manufacturer          m  with (NOLOCK) on pm.ManufacturerID = m.ManufacturerID 
        left join ProductDistributor   pd  with (NOLOCK) on p.ProductID = pd.ProductID
        left join Distributor           d  with (NOLOCK) on pd.DistributorID = d.DistributorID
        left join ExtendedPrice        ep  with (NOLOCK) on ep.VariantID = pv.VariantID and ep.CustomerLevelID = @CustomerLevelID
        left join ProductCustomerLevel pcl with (NOLOCK) on p.ProductID = pcl.ProductID and pcl.CustomerLevelID = @CustomerLevelID
        left join (select VariantID, sum(quan) quan from Inventory with (nolock) group by VariantID) i  on pv.VariantID = i.VariantID
    WHERE pf.rownum >= @pagesize*(@pagenum-1)+1 and pf.rownum <= @pagesize*(@pagenum)
    ORDER BY pf.rownum

    IF @StatsFirst <> 1
        SELECT cast(ceiling(@rcount*1.0/@pagesize) as int) pages, @rcount ProductCount

END 


This creates 3 more sort options for the "GetProducts" stored procedure.
They are: categoryprice, sectionprice, manufacturerprice (depending on which bucket needs to be sorted by price)

2. I created a new xml package only changing the "sortEntityName" parameter to one of the 3 new options I added to the stored procedure (depending on if the XML package was to be used for a category, section, or manufacturer). As seen below:

C#/VB.NET Code:

 <query name="Products" rowElementName="Product">
        <sql>
            <![CDATA[
                exec aspdnsf_GetProducts 
                    @categoryID = @CatID,
                    @sectionID = @SecID,
                    @manufacturerID = @ManID,
                    @distributorID = @DistID,
                    @genreID = @GenreID,
                    @vectorID = @VectorID,
                    @localeName = @locale,
                    @CustomerLevelID = @CustLevelID,
                    @affiliateID = @AffID,
                    @ProductTypeID = @ProdTypeID, 
                    @ViewType = 1,
                    @pagenum = @pgnum,
                    @pagesize = null,
                    @StatsFirst = 0,
                    @publishedonly = 1,
                    @ExcludePacks = 0,
                    @ExcludeKits = 0,
                    @ExcludeSysProds = 0,
                    @InventoryFilter = @InvFilter,
                    @sortEntityName = 'manufacturerprice'
            ]]>
        </sql> 


3. Set the xml package of the category/section/ or manufacturer to the new one I made. Now that bucket is sorted by price ascending!

Some additional notes:

This is static, and cannot be changed by a dropdown (i'll probably add that sometime later if we see we need it).

It sorts only ascending by price (but you can probably figure out how to change that by looking at the SQL I wrote)

For variant prices I used the MIN or smallest price among the variants (again you can change this if needed in the SP)

For Kit prices I just use the base or "default" price.

This DOES NOT affect your current sort options in the stored procedure (they will still work), it just adds some more options for the sort type in the stored procedure

Good Luck with it!!

Jason Shehane
(Self proclaimed SQL Wizard )

Well, here's my stab at enhancing your current sort enhancements. I think this adds ascending/descending to each sort and adds two more sorts for sorting with a category/manufacturer/section etc... (ie. on search page results) My only doubt is whether this will sort EVERYTHING in your DB by price and then pick out the results. Let me know what you guys think. (THIS CODE HAS NOT YET BEEN TESTED... as I don't have direct access to the SQL Server)

To use these sorts... simply set SortEntityName to one of the following non-standard sort-types:

categoryprice1 - Category-based ascending price sort
categoryprice2 - Category-based descending price sort
sectionprice1 - Section-based ascending price sort
sectionprice2 - Section-based descending price sort
mfgprice1 - Manufacturer-based ascending price sort
mfgprice2 - Manufacturer-based descending price sort
price1 - Unattached ascending price sort
price2 - Unattached descending price sort

C#/VB.NET Code:

USE [AspDotNetStorefront] 
GO 
/****** Object:  StoredProcedure [dbo].[aspdnsf_GetProducts]    Script Date: 12/31/2007 10:15:13 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
ALTER proc [dbo].[aspdnsf_GetProducts] 
    @categoryID      int = null, 
    @sectionID       int = null, 
    @manufacturerID  int = null, 
    @distributorID   int = null, 
    @genreID         int = null, 
    @vectorID        int = null, 
    @localeID        int = null, 
    @CustomerLevelID int = null, 
    @affiliateID     int = null, 
    @ProductTypeID   int = null, 
    @ViewType        bit = 1, -- 0 = all variants, 1 = one variant 
    @sortEntity      int = 0, -- 1 = category, 2 = section, 3 = manufacturer, 4 = distributor, 5= genre, 6 = vector 
    @pagenum         int = 1, 
    @pagesize        int = null, 
    @StatsFirst      tinyint = 1, 
    @searchstr       nvarchar(4000) = null, 
    @extSearch       tinyint = 0, 
    @publishedonly   tinyint = 0, 
    @ExcludePacks    tinyint = 0, 
    @ExcludeKits     tinyint = 0, 
    @ExcludeSysProds tinyint = 0, 
    @InventoryFilter int = 0,  --  will only show products with an inventory level GREATER OR EQUAL TO than the number specified in this parameter, set to -1 to disable inventory filtering 
    @sortEntityName  varchar(20) = '', -- usely only when the entity id is provided, allowed values: category, section, manufacturer, distributor, genre, vector 
    @localeName      varchar(20) = '', 
    @OnSaleOnly      tinyint = 0 
AS 
BEGIN 

    SET NOCOUNT ON  

    DECLARE @rcount int 
    DECLARE @productfilter table (rownum int not null identity  primary key, productid int not null, displayorder int not null, VariantID int not null, VariantDisplayOrder int not null, ProductName nvarchar(400) null, VariantName nvarchar(400) null) 
    DECLARE @FilterProductsByAffiliate tinyint, @FilterProductsByCustomerLevel tinyint, @HideProductsWithLessThanThisInventoryLevel int 
    --Temp table I create to give me the row number of results I sort how I want. 
    CREATE TABLE #RowNumber (displayorder int IDENTITY(1,1), productid int, categoryID int) 
    CREATE TABLE #displayorder (productid int not null primary key, displayorder int not null) 
    CREATE TABLE #inventoryfilter (productid int not null, variantid int not null, InvQty int not null) 
    CREATE CLUSTERED INDEX tmp_inventoryfilter ON #inventoryfilter (productid, variantid) 

    DECLARE @custlevelcount int, @sectioncount int, @localecount int, @affiliatecount int, @categorycount int, @CustomerLevelFilteringIsAscending bit, @distributorcount int, @genrecount int, @vectorcount int, @manufacturercount int 

    SELECT @FilterProductsByAffiliate = case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig WHERE [Name] = 'FilterProductsByAffiliate' 
    SELECT @FilterProductsByCustomerLevel = case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig WHERE [Name] = 'FilterProductsByCustomerLevel' 
    SELECT @HideProductsWithLessThanThisInventoryLevel = convert(int, ConfigValue) FROM dbo.AppConfig WHERE [Name] = 'HideProductsWithLessThanThisInventoryLevel' and isnumeric(ConfigValue) = 1 

    IF @InventoryFilter <> -1 and (@HideProductsWithLessThanThisInventoryLevel > @InventoryFilter or @HideProductsWithLessThanThisInventoryLevel  = -1) 
        SET @InventoryFilter  = @HideProductsWithLessThanThisInventoryLevel 


    SET @categoryID      = nullif(@categoryID, 0) 
    SET @sectionID       = nullif(@sectionID, 0) 
    SET @manufacturerID  = nullif(@manufacturerID, 0) 
    SET @distributorID   = nullif(@distributorID, 0) 
    SET @genreID            = nullif(@genreID, 0) 
    SET @vectorID        = nullif(@vectorID, 0) 
    SET @affiliateID     = nullif(@affiliateID, 0) 
    SET @ProductTypeID   = nullif(@ProductTypeID, 0) 


    SET @CustomerLevelFilteringIsAscending  = 0 
    SELECT @CustomerLevelFilteringIsAscending  = case configvalue when 'true' then 1 else 0 end 
    FROM dbo.AppConfig 
    WHERE name = 'FilterByCustomerLevelIsAscending' 

    IF @localeID is null and ltrim(rtrim(@localeName)) <> '' 
        SELECT @localeID = LocaleSettingID FROM LocaleSetting WHERE Name = ltrim(rtrim(@localeName)) 

    select @categorycount     = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('productcategory') and si.indid < 2 and type = 'u' 
    select @sectioncount      = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('productsection') and si.indid < 2 and type = 'u' 
    select @localecount       = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductLocaleSetting') and si.indid < 2 and type = 'u' 
    select @custlevelcount    = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductCustomerLevel') and si.indid < 2 and type = 'u' 
    select @affiliatecount    = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductAffiliate') and si.indid < 2 and type = 'u' 
    select @distributorcount  = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductDistributor') and si.indid < 2 and type = 'u' 
    select @genrecount        = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductGenre') and si.indid < 2 and type = 'u' 
    select @vectorcount       = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductVector') and si.indid < 2 and type = 'u' 
    select @manufacturercount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductManufacturer') and si.indid < 2 and type = 'u' 


    -- get page size 
    IF @pagesize is null or @pagesize = 0 BEGIN 
        IF @categoryID is not null 
            SELECT @pagesize = PageSize FROM dbo.Category WHERE categoryID = @categoryID 
        ELSE IF @sectionID is not null 
            SELECT @pagesize = PageSize FROM dbo.Section WHERE sectionID = @sectionID 
        ELSE IF @manufacturerID is not null 
            SELECT @pagesize = PageSize FROM dbo.Manufacturer WHERE manufacturerID = @manufacturerID 
        ELSE IF @distributorID is not null 
            SELECT @pagesize = PageSize FROM dbo.Distributor WHERE distributorID = @distributorID 
        ELSE IF @genreID is not null 
            SELECT @pagesize = PageSize FROM dbo.Genre WHERE genreID = @genreID 
        ELSE IF @vectorID is not null 
            SELECT @pagesize = PageSize FROM dbo.Vector WHERE vectorID = @vectorID 
        ELSE  
            SELECT @pagesize = convert(int, ConfigValue) FROM dbo.AppConfig WHERE [Name] = 'Default_CategoryPageSize' 
    END 

    IF @pagesize is null or @pagesize = 0 
        SET @pagesize = 20 

    -- get sort order 
    IF @sortEntity = 1 or @sortEntityName = 'category' BEGIN 
        INSERT #displayorder select productid, displayorder from ProductCategory where categoryID = @categoryID 
    END 
    ELSE IF @sortEntity = 2 or @sortEntityName = 'section' BEGIN 
        INSERT #displayorder select productid, displayorder from ProductSection where sectionId = @sectionID 
    END 
    ELSE IF @sortEntity = 3 or @sortEntityName = 'manufacturer' BEGIN 
        INSERT #displayorder select productid, displayorder from ProductManufacturer where ManufacturerID = @manufacturerID 
    END 
    ELSE IF @sortEntity = 4 or @sortEntityName = 'distributor' BEGIN 
        INSERT #displayorder select productid, displayorder from ProductDistributor where DistributorID = @distributorID 
    END 
    ELSE IF @sortEntity = 5 or @sortEntityName = 'genre' BEGIN 
        INSERT #displayorder select productid, displayorder from ProductGenre where GenreID = @genreID 
    END 
    ELSE IF @sortEntity = 6 or @sortEntityName = 'vector' BEGIN 
        INSERT #displayorder select productid, displayorder from ProductVector where VectorID = @vectorID 
    END 
    ELSE IF @sortEntity = 7 or @sortEntityName = 'categoryprice1' BEGIN 

        INSERT #RowNumber (productID, categoryID) 
        SELECT PC.Productid, PC.CategoryID 
        FROM ProductCategory PC INNER JOIN ProductVariant PV 
            ON PC.productID = PV.productID 
        WHERE PC.CategoryID = @categoryID 
        GROUP BY PC.ProductID, PC.CategoryID 
        ORDER BY MIN(PV.Price) 

        INSERT #displayorder select productid, displayorder from #RowNumber where categoryID = @categoryID 
    END 
    ELSE IF @sortEntity = 8 or @sortEntityName = 'categoryprice2' BEGIN 

        INSERT #RowNumber (productID, categoryID) 
        SELECT PC.Productid, PC.CategoryID 
        FROM ProductCategory PC INNER JOIN ProductVariant PV 
            ON PC.productID = PV.productID 
        WHERE PC.CategoryID = @categoryID 
        GROUP BY PC.ProductID, PC.CategoryID 
        ORDER BY MIN(PV.Price) DESC 

        INSERT #displayorder select productid, displayorder from #RowNumber where categoryID = @categoryID 
    END 
    ELSE IF @sortEntity = 9 or @sortEntityName = 'sectionprice1' BEGIN 

        INSERT #RowNumber (productID, categoryID) 
        SELECT PS.Productid, PS.SectionID 
        FROM ProductSection PS INNER JOIN ProductVariant PV 
            ON PS.productID = PV.productID 
        WHERE PS.SectionID = @sectionID 
        GROUP BY PS.ProductID, PS.SectionID 
        ORDER BY MIN(PV.Price) 

        INSERT #displayorder select productid, displayorder from #RowNumber where categoryID = @sectionID 
    END 
    ELSE IF @sortEntity = 10 or @sortEntityName = 'sectionprice2' BEGIN 

        INSERT #RowNumber (productID, categoryID) 
        SELECT PS.Productid, PS.SectionID 
        FROM ProductSection PS INNER JOIN ProductVariant PV 
            ON PS.productID = PV.productID 
        WHERE PS.SectionID = @sectionID 
        GROUP BY PS.ProductID, PS.SectionID 
        ORDER BY MIN(PV.Price) DESC 

        INSERT #displayorder select productid, displayorder from #RowNumber where categoryID = @sectionID 
    END 
    ELSE IF @sortEntity = 11 or @sortEntityName = 'mfgprice1' BEGIN 

        INSERT #RowNumber (productID, categoryID) 
        SELECT PM.Productid, PM.ManufacturerID 
        FROM ProductManufacturer PM INNER JOIN ProductVariant PV 
            ON PM.productID = PV.productID 
        WHERE PM.ManufacturerID = @ManufacturerID 
        GROUP BY PM.ProductID, PM.ManufacturerID 
        ORDER BY MIN(PV.Price) 

        INSERT #displayorder select productid, displayorder from #RowNumber where categoryID = @ManufacturerID 
    END 
    ELSE IF @sortEntity = 12 or @sortEntityName = 'mfgprice2' BEGIN 

        INSERT #RowNumber (productID, categoryID) 
        SELECT PM.Productid, PM.ManufacturerID 
        FROM ProductManufacturer PM INNER JOIN ProductVariant PV 
            ON PM.productID = PV.productID 
        WHERE PM.ManufacturerID = @ManufacturerID 
        GROUP BY PM.ProductID, PM.ManufacturerID 
        ORDER BY MIN(PV.Price) DESC 

        INSERT #displayorder select productid, displayorder from #RowNumber where categoryID = @ManufacturerID 
    END 
    ELSE IF @sortEntity = 13 or @sortEntityName = 'price1' BEGIN 

        INSERT #RowNumber (productID, categoryID) 
        SELECT PV.Productid 
        FROM ProductVariant PV 
        GROUP BY PV.ProductID
        ORDER BY MIN(PV.Price)

        INSERT #displayorder select productid, displayorder from #RowNumber
    END 
    ELSE IF @sortEntity = 14 or @sortEntityName = 'price2' BEGIN 

        INSERT #RowNumber (productID, categoryID) 
        SELECT PV.Productid 
        FROM ProductVariant PV 
        GROUP BY PV.ProductID
        ORDER BY MIN(PV.Price) DESC

        INSERT #displayorder select productid, displayorder from #RowNumber
    END 
    ELSE BEGIN 
        INSERT #displayorder select productid, 1 from Product ORDER BY Name 
    END 



    SET @searchstr = '%' + rtrim(ltrim(@searchstr)) + '%' 

    IF @InventoryFilter <> -1 BEGIN 

        IF @ViewType = 1 BEGIN 
            INSERT #inventoryfilter 
            SELECT p.productid, pv.VariantID, sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) invqty 
            FROM product p with (NOLOCK) join #displayorder d on p.ProductID = d.ProductID 
                join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID  and pv.IsDefault = 1 
                left join Inventory i with (NOLOCK) on pv.VariantID = i.VariantID 
            GROUP BY p.productid, pv.VariantID 
            HAVING sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) >= @InventoryFilter 
        END 
        ELSE 
            INSERT #inventoryfilter 
            SELECT p.productid, pv.VariantID, sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) invqty 
            FROM product p with (NOLOCK) join #displayorder d on p.ProductID = d.ProductID 
                join ProductVariant pv with (NOLOCK) on p.ProductID = pv.ProductID 
                left join Inventory i with (NOLOCK) on pv.VariantID = i.VariantID 
            GROUP BY p.productid, pv.VariantID 
            HAVING sum(case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end ) >= @InventoryFilter 


        INSERT @productfilter (productid, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName) 
        SELECT distinct p.productid, do.displayorder, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name 
        FROM  
            product p with (nolock) 
            join #displayorder do on p.ProductID = do.ProductID  
            left join ProductVariant pv        with (NOLOCK) ON p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType 
            left join productcategory pc       with (nolock) on p.ProductID = pc.ProductID  
            left join productsection ps        with (nolock) on p.ProductID = ps.ProductID  
            left join ProductManufacturer pm   with (nolock) on p.ProductID = pm.ProductID  
            left join ProductDistributor pd    with (nolock) on p.ProductID = pd.ProductID  
            left join ProductGenre px          with (nolock) on p.ProductID = px.ProductID  
            left join ProductVector px2        with (nolock) on p.ProductID = px2.ProductID  
            left join ProductLocaleSetting pl  with (nolock) on p.ProductID = pl.ProductID  
            left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID  
            left join ProductAffiliate pa      with (nolock) on p.ProductID = pa.ProductID  
            join #inventoryfilter i on pv.VariantID = i.VariantID 
        WHERE  
              (pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0) 
          and (ps.sectionid = @sectionID or @sectionID is null or @sectioncount = 0) 
          and (pl.LocaleSettingID = @localeID or @localeID is null or @localecount = 0) 
          and (pa.AffiliateID = @affiliateID or pa.AffiliateID is null or @affiliatecount = 0 or @FilterProductsByAffiliate = 0) 
          and (pm.manufacturerid = @manufacturerID or @manufacturerID is null or @manufacturercount = 0) 
          and (pd.DistributorID = @distributorID or @distributorID is null or @distributorcount = 0) 
          and (px.GenreID = @genreID or @genreID is null or @genrecount = 0) 
          and (px2.VectorID = @vectorID or @vectorID is null or @vectorcount = 0) 
          and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID) 
          and (case  
                when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null then 1 
                when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1  
                when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1  
                else 0 
               end  = 1 
              ) 
          and ( 
                 @searchstr is null 
              or patindex(@searchstr, isnull(p.name, '')) > 0 
              or patindex(@searchstr, isnull(convert(nvarchar(20),p.productid), '')) > 0  
              or patindex(@searchstr, isnull(pv.name, '')) > 0 
              or patindex(@searchstr, isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0 
              or patindex(@searchstr, isnull(p.manufacturerpartnumber, '')) > 0 
              or patindex(@searchstr, isnull(pv.manufacturerpartnumber, '')) > 0 
              or (patindex(@searchstr, isnull(p.Description, '')) > 0 and @extSearch = 1) 
              or (patindex(@searchstr, isnull(p.Summary, '')) > 0 and @extSearch = 1) 
              ) 
          and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly 
          and p.published >= @publishedonly 
          and pv.published >= @publishedonly 
          and isnull(p.IsAPack, 0) <= 1-@ExcludePacks 
          and isnull(p.IsAKit, 0) <= 1-@ExcludeKits 
          and p.IsSystem <= 1-@ExcludeSysProds 
          and p.Deleted = 0 
          and pv.Deleted = 0 
          and getdate() between isnull(p.AvailableStartDate, '1/1/1900') and isnull(p.AvailableStopDate, '1/1/2999') 
        order by do.displayorder, p.Name, pv.DisplayOrder, pv.Name 

    END  
    ELSE BEGIN 
        INSERT @productfilter (productid, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName) 
        SELECT distinct p.productid, do.displayorder, pv.VariantID, pv.DisplayOrder, p.Name, pv.Name 
        FROM  
            product p with (nolock) 
            join #displayorder do on p.ProductID = do.ProductID  
            join ProductVariant pv             with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType 
            left join productcategory pc       with (nolock) on p.ProductID = pc.ProductID  
            left join productsection ps        with (nolock) on p.ProductID = ps.ProductID  
            left join ProductManufacturer pm   with (nolock) on p.ProductID = pm.ProductID  
            left join ProductDistributor pd    with (nolock) on p.ProductID = pd.ProductID  
            left join ProductGenre px          with (nolock) on p.ProductID = px.ProductID  
            left join ProductVector px2        with (nolock) on p.ProductID = px2.ProductID  
            left join ProductLocaleSetting pl  with (nolock) on p.ProductID = pl.ProductID  
            left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID  
            left join ProductAffiliate pa      with (nolock) on p.ProductID = pa.ProductID  
        WHERE  
              (pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0) 
          and (ps.sectionid = @sectionID or @sectionID is null or @sectioncount = 0) 
          and (pl.LocaleSettingID = @localeID or @localeID is null or @localecount = 0) 
          and (pa.AffiliateID = @affiliateID or pa.AffiliateID is null or @affiliatecount = 0 or @FilterProductsByAffiliate = 0) 
          and (pm.manufacturerid = @manufacturerID or @manufacturerID is null or @manufacturercount = 0) 
          and (pd.DistributorID = @distributorID or @distributorID is null or @distributorcount = 0) 
          and (px.GenreID = @genreID or @genreID is null or @genrecount = 0) 
          and (px2.VectorID = @vectorID or @vectorID is null or @vectorcount = 0) 
          and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID) 
          and (case  
                when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null then 1 
                when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1  
                when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1  
                else 0 
               end  = 1 
              ) 
          and ( 
                 @searchstr is null 
              or patindex(@searchstr, isnull(p.name, '')) > 0 
              or patindex(@searchstr, isnull(convert(nvarchar(20),p.productid), '')) > 0  
              or patindex(@searchstr, isnull(pv.name, '')) > 0 
              or patindex(@searchstr, isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0 
              or patindex(@searchstr, isnull(p.manufacturerpartnumber, '')) > 0 
              or patindex(@searchstr, isnull(pv.manufacturerpartnumber, '')) > 0 
              or (patindex(@searchstr, isnull(p.Description, '')) > 0 and @extSearch = 1) 
              or (patindex(@searchstr, isnull(p.Summary, '')) > 0 and @extSearch = 1) 
              ) 
          and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly 
          and p.published >= @publishedonly 
          and pv.published >= @publishedonly 
          and isnull(p.IsAPack, 0) <= 1-@ExcludePacks 
          and isnull(p.IsAKit, 0) <= 1-@ExcludeKits 
          and p.IsSystem <= 1-@ExcludeSysProds 
          and p.Deleted = 0 
          and pv.Deleted = 0 
          and getdate() between isnull(p.AvailableStartDate, '1/1/1900') and isnull(p.AvailableStopDate, '1/1/2999') 
        order by do.displayorder, p.Name, pv.DisplayOrder, pv.Name 

    END 

    SET @rcount = @@rowcount 
    IF @StatsFirst = 1 
        SELECT cast(ceiling(@rcount*1.0/@pagesize) as int) pages, @rcount ProductCount 

    SELECT  
        p.ProductID, 
        p.Name, 
        pv.VariantID, 
        pv.Name AS VariantName, 
        p.ProductGUID, 
        p.Summary, 
        p.Description, 
        p.SEKeywords, 
        p.SEDescription, 
        p.SpecTitle, 
        p.MiscText, 
        p.SwatchImageMap, 
        p.IsFeaturedTeaser, 
        p.FroogleDescription, 
        p.SETitle, 
        p.SENoScript, 
        p.SEAltText, 
        p.SizeOptionPrompt, 
        p.ColorOptionPrompt, 
        p.TextOptionPrompt, 
        p.ProductTypeID, 
        p.TaxClassID, 
        p.SKU, 
        p.ManufacturerPartNumber, 
        p.SalesPromptID, 
        p.SpecCall, 
        p.SpecsInline, 
        p.IsFeatured, 
        p.XmlPackage, 
        p.ColWidth, 
        p.Published, 
        p.RequiresRegistration, 
        p.Looks, 
        p.Notes, 
        p.QuantityDiscountID, 
        p.RelatedProducts, 
        p.UpsellProducts, 
        p.UpsellProductDiscountPercentage, 
        p.RelatedDocuments, 
        p.TrackInventoryBySizeAndColor, 
        p.TrackInventoryBySize, 
        p.TrackInventoryByColor, 
        p.IsAKit, 
        p.ShowInProductBrowser, 
        p.IsAPack, 
        p.PackSize, 
        p.ShowBuyButton, 
        p.RequiresProducts, 
        p.HidePriceUntilCart, 
        p.IsCalltoOrder, 
        p.ExcludeFromPriceFeeds, 
        p.RequiresTextOption, 
        p.TextOptionMaxLength, 
        p.SEName, 
        p.Deleted, 
        p.CreatedOn, 
        p.ImageFileNameOverride, 
        pv.VariantGUID, 
        pv.Description AS VariantDescription, 
        pv.SEKeywords AS VariantSEKeywords, 
        pv.SEDescription AS VariantSEDescription, 
        pv.Colors, 
        pv.ColorSKUModifiers, 
        pv.Sizes, 
        pv.SizeSKUModifiers, 
        pv.FroogleDescription AS VariantFroogleDescription, 
        pv.SKUSuffix, 
        pv.ManufacturerPartNumber AS VariantManufacturerPartNumber, 
        pv.Price, 
        pv.CustomerEntersPrice,  
        pv.CustomerEntersPricePrompt, 
        isnull(pv.SalePrice, 0) SalePrice, 
        cast(isnull(pv.Weight,0) as decimal(10,1)) Weight, 
        pv.MSRP, 
        pv.Cost, 
        isnull(pv.Points,0) Points, 
        pv.Dimensions, 
        case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end Inventory, 
        pv.DisplayOrder as VariantDisplayOrder, 
        pv.Notes AS VariantNotes, 
        pv.IsTaxable, 
        pv.IsShipSeparately, 
        pv.IsDownload, 
        pv.DownloadLocation, 
        pv.Published AS VariantPublished, 
        pv.IsSecureAttachment, 
        pv.IsRecurring, 
        pv.RecurringInterval, 
        pv.RecurringIntervalType, 
        pv.SubscriptionInterval, 
        pv.SEName AS VariantSEName, 
        pv.RestrictedQuantities, 
        pv.MinimumQuantity, 
        pv.Deleted AS VariantDeleted, 
        pv.CreatedOn AS VariantCreatedOn, 
        d.Name AS DistributorName, 
        d.DistributorID, 
        d.SEName AS DistributorSEName, 
        m.ManufacturerID, 
        m.Name AS ManufacturerName, 
        m.SEName AS ManufacturerSEName, 
        s.Name AS SalesPromptName, 
        case when pcl.productid is null then 0 else isnull(ep.Price, 0) end ExtendedPrice 
    FROM Product p with (NOLOCK)  
        left join ProductVariant       pv  with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType 
        join @productfilter            pf                on pv.ProductID = pf.ProductID and pv.VariantID = pf.VariantID  
        left join SalesPrompt           s  with (NOLOCK) on p.SalesPromptID = s.SalesPromptID  
        left join ProductManufacturer  pm  with (NOLOCK) on p.ProductID = pm.ProductID  
        left join Manufacturer          m  with (NOLOCK) on pm.ManufacturerID = m.ManufacturerID  
        left join ProductDistributor   pd  with (NOLOCK) on p.ProductID = pd.ProductID 
        left join Distributor           d  with (NOLOCK) on pd.DistributorID = d.DistributorID 
        left join ExtendedPrice        ep  with (NOLOCK) on ep.VariantID = pv.VariantID and ep.CustomerLevelID = @CustomerLevelID 
        left join ProductCustomerLevel pcl with (NOLOCK) on p.ProductID = pcl.ProductID and pcl.CustomerLevelID = @CustomerLevelID 
        left join (select VariantID, sum(quan) quan from Inventory with (nolock) group by VariantID) i  on pv.VariantID = i.VariantID 
    WHERE pf.rownum >= @pagesize*(@pagenum-1)+1 and pf.rownum <= @pagesize*(@pagenum) 
    ORDER BY pf.rownum 

    IF @StatsFirst <> 1 
        SELECT cast(ceiling(@rcount*1.0/@pagesize) as int) pages, @rcount ProductCount 

END 


Thanks for the work fellas! Unfortunately that does nothing that we couldn't already do manually through the admin panel

If you could figure out how to stick a drop down box on that so the customers viewing the store could change it, it would be superb

I would just write some javascript to change a param request mapped to the @SortEntity sql parameter. I'll post some code when it is completed.

Could you modify this SQL to FILTER results, by category / section? So you could apply a sort and a filter at the same time?

I basically want to is so when looking at category product results, can filter by section and sort by the options you have already shown...

Funny you should ask...

I'm about to post methods for sort and filtering.

I've completely redone the XML packages and aspdnsf_GetProducts to add a bunch of features. GetProducts now returns all matching products, a list of manufacturers & categories those products are in and the number of products in each cat/mfg (for cat/mfg filtering), and a set of up to 10 automatically generated price ranges for price level filtering. Moreover, the search function has been redesigned to take each "word" as a keyword and searches as if you had FULL TEXT indexing enabled without having to do anything funky to your database. In the next few weeks I should have the entire bundle posted.

A few words of caution. These will be EXTREMELY advanced bundles that WILL NOT be complete in the sense of every package being rewritten. Any packages that are not included in the to-be posted zip file will NOT FUNCTION with the new GetProducts routine (at least they have not been tested). Moreover, the whole skin system has been revamped in my packages, as category and manufacturer lists are now DIRECTLY RELATED to the product results, only displaying manufacturers and categories that are relevant to the current search/browse. You might break your system trying these solutions, as they are much more difficult than the previously posted GetProducts/searchadv.aspx updates. These are complete replacements for parts of ASPDNSF. Anyway... just thought I'd warn everybody before everyone gets too excited.

Check out www.wholesalemusic.com to see how some of these enhancements work (price ranges aren't up yet, but should be finished soon).

Browse by Manufacturer... then FILTER BY CATEGORY!!!
Browse by Category... then FILTER BY MANUFACTURER!!!

Yay!! Simple features that really do wonders for the intellibility of the UI.

Let me know if you guys find any bugs... I've already found one that caused an infinite loop on my SQL Server (it eventually timed out... but its fixed now)

Did you ever figure this out? Id love to know how to do it. ....

Originally Posted by MikeD

Funny you should ask...

I'm about to post methods for sort and filtering.

I've completely redone the XML packages and aspdnsf_GetProducts to add a bunch of features. GetProducts now returns all matching products, a list of manufacturers & categories those products are in and the number of products in each cat/mfg (for cat/mfg filtering), and a set of up to 10 automatically generated price ranges for price level filtering. Moreover, the search function has been redesigned to take each "word" as a keyword and searches as if you had FULL TEXT indexing enabled without having to do anything funky to your database. In the next few weeks I should have the entire bundle posted.

A few words of caution. These will be EXTREMELY advanced bundles that WILL NOT be complete in the sense of every package being rewritten. Any packages that are not included in the to-be posted zip file will NOT FUNCTION with the new GetProducts routine (at least they have not been tested). Moreover, the whole skin system has been revamped in my packages, as category and manufacturer lists are now DIRECTLY RELATED to the product results, only displaying manufacturers and categories that are relevant to the current search/browse. You might break your system trying these solutions, as they are much more difficult than the previously posted GetProducts/searchadv.aspx updates. These are complete replacements for parts of ASPDNSF. Anyway... just thought I'd warn everybody before everyone gets too excited.

Check out www.wholesalemusic.com to see how some of these enhancements work (price ranges aren't up yet, but should be finished soon).

Browse by Manufacturer... then FILTER BY CATEGORY!!!
Browse by Category... then FILTER BY MANUFACTURER!!!

Yay!! Simple features that really do wonders for the intellibility of the UI.

Let me know if you guys find any bugs... I've already found one that caused an infinite loop on my SQL Server (it eventually timed out... but its fixed now)

how would you sort by rating?

Code:

ELSE IF @sortEntityName = 5 BEGIN --Sort By Rating

        INSERT #RowNumber (productID) 
        SELECT Rating 
        FROM Rating RT 
        GROUP BY RT.ProductID
        ORDER BY Rating

        INSERT #displayorder select productid, displayorder from #RowNumber
END