deanglen
Yak Posting Veteran
64 Posts 
Posted  02/20/2013 : 08:14:51

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, p.ExtensionData, case when pcl.productid is null then 0 else isnull(ep.Price, 0) end ExtendedPrice FROM dbo.Product p with (NOLOCK) left join dbo.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 dbo.SalesPrompt s with (NOLOCK) on p.SalesPromptID = s.SalesPromptID left join dbo.ProductManufacturer pm with (NOLOCK) on p.ProductID = pm.ProductID left join dbo.Manufacturer m with (NOLOCK) on pm.ManufacturerID = m.ManufacturerID left join dbo.ProductDistributor pd with (NOLOCK) on p.ProductID = pd.ProductID left join dbo.Distributor d with (NOLOCK) on pd.DistributorID = d.DistributorID left join dbo.ExtendedPrice ep with (NOLOCK) on ep.VariantID = pv.VariantID and ep.CustomerLevelID = @CustomerLevelID left join dbo.ProductCustomerLevel pcl with (NOLOCK) on p.ProductID = pcl.ProductID and pcl.CustomerLevelID = @CustomerLevelID left join (select VariantID, sum(quan) quan from dbo.Inventory with (nolock) group by VariantID) i on pv.VariantID = i.VariantID WHERE pf.rownum >= @pagesize*(@pagenum1)+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


