Thread: Related Product Price

Hi,

I'm currently working on the related products within my site, but unlike the Upsell XML package, I can not pull in the price and msrp price into my related products section.

I am using AspDotNetStorefront ML 8.0.1.2 and I have looked through these forums applying the instructions given out by many on here, but still no luck.

Here is the code I'm using

alter PROCEDURE [dbo].[aspdnsf_GetCustomersRelatedProducts]
@CustomerViewID nvarchar(50),
@ProductID int,
@CustomerLevelID int,
@InvFilter int,
@affiliateID int
--WITH ENCRYPTION
AS
SET NOCOUNT ON

DECLARE
@custlevelcount int,
@CustomerLevelFilteringIsAscending bit,
@FilterProductsByCustomerLevel tinyint,
@relatedprods varchar(8000),
@DynamicProductsEnabled varchar(10),
@DynamicProductsDisplayed int,
@FilterProductsByAffiliate tinyint,
@affiliatecount int,
@AffiliateExists int


SELECT @custlevelcount = si.rows from dbo.sysobjects so with (nolock) join dbo.sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductCustomerLevel') and si.indid < 2 and type = 'u'
SELECT @FilterProductsByCustomerLevel = case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'FilterProductsByCustomerLevel'
SELECT @FilterProductsByAffiliate = case ConfigValue when 'true' then 1 else 0 end FROM dbo.AppConfig with (nolock) WHERE [Name] = 'FilterProductsByAffiliate'
SELECT @affiliatecount = count(*), @AffiliateExists = sum(case when AffiliateID = @affiliateID then 1 else 0 end) from dbo.ProductAffiliate with (nolock) where ProductID = @ProductID


SET @CustomerLevelFilteringIsAscending = 0
SELECT @CustomerLevelFilteringIsAscending = case configvalue when 'true' then 1 else 0 end
FROM dbo.appConfig with (nolock)
WHERE name like 'FilterByCustomerLevelIsAscending'

SELECT @DynamicProductsDisplayed = CAST(ConfigValue AS INT) from AppConfig with (NOLOCK) where Name = 'DynamicRelatedProducts.NumberDisplayed'
SELECT @DynamicProductsEnabled = CASE ConfigValue WHEN 'true' then 1 else 0 end from AppConfig with (NOLOCK) where Name = 'DynamicRelatedProducts.Enabled'
select @relatedprods = replace(cast(relatedproducts as varchar(8000)), ' ', '') from dbo.product with (NOLOCK) where productid = @productid

--IF(@DynamicProductsEnabled = 1 and @DynamicProductsDisplayed > 0)
BEGIN
SELECT TOP (@DynamicProductsDisplayed) tp.ProductID, tp.ProductGUID, tp.ImageFilenameOverride, tp.SKU, tp.SEAltText, tp.Name, tp.Description,
pv.VariantID,
pv.name VariantName,
pv.Price,
pv.Description VariantDescription,
isnull(pv.SalePrice, 0) SalePrice, isnull(pv.SkuSuffix, '') SkuSuffix,
pv.Dimensions, pv.Weight, isnull(pv.Points, 0) Points, pv.Inventory,
pv.ImageFilenameOverride VariantImageFilenameOverride, pv.isdefault,
sp.name SalesPromptName,
case when pcl.productid is null then 0 else isnull(e.Price, 0) end ExtendedPrice
FROM Product tp with (NOLOCK)
join dbo.productvariant pv with (NOLOCK) on tp.ProductID = pv.ProductID
join dbo.SalesPrompt sp with (NOLOCK) on tp.SalesPromptID = sp.SalesPromptID
left join dbo.ExtendedPrice e with (NOLOCK) on pv.VariantID=e.VariantID and e.CustomerLevelID = @CustomerLevelID
left join dbo.productcustomerlevel pcl with (nolock) on tp.productid = pcl.productid and @FilterProductsByCustomerLevel = 1
JOIN
(
SELECT p.ProductID, p.ProductGUID, p.ImageFilenameOverride, p.SKU, p.SEAltText, p.Name, p.Description,
pv.VariantID,
pv.name VariantName,
pv.Price,
pv.Description VariantDescription,
isnull(pv.SalePrice, 0) SalePrice, isnull(pv.SkuSuffix, '') SkuSuffix,
pv.Dimensions, pv.Weight, isnull(pv.Points, 0) Points, pv.Inventory,
pv.ImageFilenameOverride VariantImageFilenameOverride, pv.isdefault,
sp.name SalesPromptName,
case when pcl.productid is null then 0 else isnull(e.Price, 0) end ExtendedPrice

from dbo.product p with (nolock)
join dbo.productvariant pv with (NOLOCK) on p.ProductID = pv.ProductID
join dbo.SalesPrompt sp with (NOLOCK) on p.SalesPromptID = sp.SalesPromptID
left join dbo.ExtendedPrice e with (NOLOCK) on pv.VariantID=e.VariantID and e.CustomerLevelID = @CustomerLevelID
join dbo.split(@relatedprods, ',') rp on p.productid = cast(rp.items as int)
left join dbo.productcustomerlevel pcl with (nolock) on p.productid = pcl.productid and @FilterProductsByCustomerLevel = 1
join (select p.ProductID
from dbo.product p with (nolock)
join dbo.split(@relatedprods, ',') rp on p.productid = cast(rp.items as int)
join (select ProductID, sum(Inventory) Inventory from dbo.productvariant with (nolock) group by ProductID) pv on p.ProductID = pv.ProductID
left join (select ProductID, sum(quan) inventory from dbo.inventory i1 with (nolock) join dbo.productvariant pv1 with (nolock) on pv1.variantid = i1.variantid join dbo.split(@relatedprods, ',') rp1 on pv1.productid = cast(rp1.items as int) group by pv1.productid) i on i.productid = p.productid
where case p.TrackInventoryBySizeAndColor when 1 then isnull(i.inventory, 0) else pv.inventory end >= @InvFilter
) tp on p.productid = tp.productid
where p.published = 1 and p.deleted = 0 and p.productid != @productid
and PV.published = 1 and PV.deleted = 0
and GETDATE() BETWEEN ISNULL(AvailableStartDate, '1/1/1900') AND ISNULL(AvailableStopDate, '1/1/2999')
and case
when @FilterProductsByCustomerLevel = 0 then 1
when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID or pcl.CustomerLevelID is null then 1
when @CustomerLevelID=0 and pcl.CustomerLevelID is null then 1
when @CustomerLevelID is null or @custlevelcount = 0 then 1
when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1
else 0
end = 1
UNION ALL
SELECT pr.ProductID, pr.ProductGUID, pr.ImageFilenameOverride, pr.SKU, pr.SEAltText, pr.Name, pr.Description,
pv.VariantID,
pv.name VariantName,
pv.Price,
pv.Description VariantDescription,
isnull(pv.SalePrice, 0) SalePrice, isnull(SkuSuffix, '') SkuSuffix,
pv.Dimensions, pv.Weight, isnull(pv.Points, 0) Points, pv.Inventory,
pv.ImageFilenameOverride VariantImageFilenameOverride, pv.isdefault,
sp.name SalesPromptName,
case when pcl.productid is null then 0 else isnull(e.Price, 0) end ExtendedPrice

FROM Product pr WITH (NOLOCK)
join dbo.productvariant pv with (NOLOCK) on pr.ProductID = pv.ProductID
join dbo.SalesPrompt sp with (NOLOCK) on pr.SalesPromptID = sp.SalesPromptID
left join dbo.ExtendedPrice e with (NOLOCK) on pv.VariantID=e.VariantID and e.CustomerLevelID = @CustomerLevelID
left join dbo.productcustomerlevel pcl with (nolock) on pr.productid = pcl.productid and @FilterProductsByCustomerLevel = 1
WHERE pr.ProductID in (
SELECT TOP 100 PERCENT p.ProductID FROM Product p with (NOLOCK)
JOIN
(
SELECT ProductID FROM ProductView with (NOLOCK) WHERE CustomerViewID
IN
(
SELECT CustomerViewID FROM ProductView with (NOLOCK)
WHERE ProductID = @ProductID AND CustomerViewID <> @CustomerViewID
)
AND ProductID <> @ProductID
AND ProductID NOT
IN
(
select ProductID
from product with (NOLOCK)
join split(@relatedprods, ',') rp on productid = cast(rp.items as int)
group by ProductID
)
) a on p.ProductID = a.ProductID
LEFT JOIN dbo.productcustomerlevel pcl with (NOLOCK) on p.productid = pcl.productid and @FilterProductsByCustomerLevel = 1
left join dbo.ProductAffiliate pa with (nolock) on p.ProductID = pa.ProductID
WHERE
pr.Published = 1 AND pr.Deleted = 0 and PV.published = 1 and PV.deleted = 0
and GETDATE() BETWEEN ISNULL(AvailableStartDate, '1/1/1900') AND ISNULL(AvailableStopDate, '1/1/2999')
and case
when @FilterProductsByCustomerLevel = 0 then 1
when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID or pcl.CustomerLevelID is null then 1
when @CustomerLevelID=0 and pcl.CustomerLevelID is null then 1
when @CustomerLevelID is null or @custlevelcount = 0 then 1
when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1
else 0
end = 1
and (pa.AffiliateID = @affiliateID or pa.AffiliateID is null or @affiliatecount = 0 or @FilterProductsByAffiliate = 0)
group by p.ProductID
order by COUNT(*) desc
)
)prd on tp.ProductID = prd.ProductID


END

IF(@DynamicProductsEnabled = 0 and @DynamicProductsDisplayed > 0)
BEGIN
select TOP (@DynamicProductsDisplayed) p.ProductID, p.ProductGUID, p.ImageFilenameOverride, p.SKU, p.SEAltText, p.Name, p.Description,
/* mb 11/09 */
pv.VariantID,
pv.name
VariantName,
pv.Price,
pv.Description VariantDescription,
isnull(pv.SalePrice, 0) SalePrice, isnull(SkuSuffix, '') SkuSuffix,
pv.Dimensions, pv.Weight, isnull(pv.Points, 0) Points, pv.Inventory,
pv.ImageFilenameOverride VariantImageFilenameOverride, pv.isdefault,
sp.name SalesPromptName,
case when pcl.productid is null then 0 else isnull(e.Price, 0) end ExtendedPrice
from dbo.product p with (nolock)
join dbo.productvariant pv with (NOLOCK) on p.ProductID = pv.ProductID
join dbo.SalesPrompt sp with (NOLOCK) on p.SalesPromptID = sp.SalesPromptID
left join dbo.ExtendedPrice e with (NOLOCK) on pv.VariantID=e.VariantID and e.CustomerLevelID = @CustomerLevelID
left join (select variantid, sum(quan) inventory from inventory group by variantid) i on pv.variantid = i.variantid
join dbo.split(@relatedprods, ',') rp on p.productid = cast(rp.items as int)
left join dbo.productcustomerlevel pcl with (nolock) on p.productid = pcl.productid and @FilterProductsByCustomerLevel = 1
join (select p.ProductID
from dbo.product p with (nolock)
join dbo.split(@relatedprods, ',') rp on p.productid = cast(rp.items as int)
join (select ProductID, sum(Inventory) Inventory from dbo.productvariant with (nolock) group by ProductID) pv on p.ProductID = pv.ProductID
left join (select ProductID, sum(quan) inventory from dbo.inventory i1 with (nolock) join dbo.productvariant pv1 with (nolock) on pv1.variantid = i1.variantid join dbo.split(@relatedprods, ',') rp1 on pv1.productid = cast(rp1.items as int) group by pv1.productid) i on i.productid = p.productid
where case p.TrackInventoryBySizeAndColor when 1 then isnull(i.inventory, 0) else pv.inventory end >= @InvFilter
) tp on p.productid = tp.productid
where p.published = 1 and p.deleted = 0 and p.productid != @productid
and pv.published = 1 and pv.deleted = 0
and GETDATE() BETWEEN ISNULL(AvailableStartDate, '1/1/1900') AND ISNULL(AvailableStopDate, '1/1/2999')
and case
when @FilterProductsByCustomerLevel = 0 then 1
when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID or pcl.CustomerLevelID is null then 1
when @CustomerLevelID=0 and pcl.CustomerLevelID is null then 1
when @CustomerLevelID is null or @custlevelcount = 0 then 1
when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1
else 0
end = 1

And this is the error I am receiving:

Exception=Incorrect syntax near the keyword 'PROCEDURE'. Must declare the scalar variable "@CustomerViewID". Incorrect syntax near ')'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Incorrect syntax near '1'.

Any help would be greatly appreciated as this is driving me nuts at the moment!

Many Thanks.

Did you try just copying the upsell XML Package, instead of trying to re-build a query?

no, the code above is a piece of code which I found on these forms to help pull the prices into the related product xml package. But it was unsuccessful.

Any ideas on how to pull the price into this xmlpackage like the upsell xml package does?

Hi All,

i m also facing the same problem. i also want to show price in related products any one knows how to do that....?

your help would be appreciated...