Show price - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Show price

Author

Topic

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-03-25 : 05:14:49

I have a product page that shows a product price. The line that displays this is

<div id="price">
<xsl:value-of select="aspdnsf:GetVariantPrice(VariantID, number(HidePriceUntilCart), Price, SalePrice, ExtendedPrice, Points, $pSalesPromptName, TaxClassID)" disable-output-escaping="yes" />
</div>

On the same page there are related products that are called from a Stored Procedure. The SQL that runs this is

select TOP (@DynamicProductsDisplayed) p.ProductID, p.ProductGUID, p.ImageFilenameOverride, p.SKU, p.sename,p.SEAltText, p.Name, p.Description, VariantID, Price,SalePrice
from dbo.product p with (nolock)
join dbo.split(@relatedprods, ',') rp on p.productid = cast(rp.items as int)
left join dbo.PRoductVariant pv with (nolock) on pv.productid = p.productid and pv.Deleted=0 and pv.Published = 1 and pv.IsDefault = 1
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
inner join (select distinct a.ProductID from Product a with (nolock) left join ProductStore b with (nolock) on a.ProductID = b.ProductID where (@filterProduct = 0 or StoreID = @storeID)) ps on p.ProductID = ps.ProductID

where
p.published = 1 and p.deleted = 0 and p.productid != @productid
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
END

When I try to get the price showing in the Related Products area I get this error

Message:Error Calling XSLTExtension Function GetVariantPrice: Invalid value specified for SalePrice ()
Stack Trace:

Any ideas where I am going wrong?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-25 : 14:40:54

not sure but I think the issue is not with the sql code, since I dont see GetVariantPrice used anywhere in the sql code.... did you try checking the sql independently if it executes without any error? If not, check it ....

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources