Yet another "BFG needs SQL help" thread...

[BFG 9000]

Greetings All,


I'm trying to craft the following into a single query & wondered if anyone has done something close that I can tweak...



1. I need to return 3 x ProductIDs
2. They must be published.
3. They must have a stock level > 0 (assume we only have a single variant).
4. The Product ID's should be determined as follows :-

A. Ideally they will be 3 random selections from a comma seperated list in the RelatedProducts field.
B. If there aren't 3 (published & in stock) ProductIDs in the RelatedProducts field then they should be topped up with random (published & in stock) ProductIDs from the same Category.


Points to note :-

The RelatedProducts field can have anything from zero to 20+ comma separated ProductIDs.
This will be running in an xmlpackage that is called from the product xmlpackage. Therefore I can pass in the current ProductID & the current CategoryID.



Erm.... Think that's it.

Problems I've hit are actually getting the string of comma seperated ProductIDs from the RelatedProduct field into a usable format i.e. numbers to use in a where clause..


I'd be grateful for any assistance you can offer.



TTFN

BFG

[BFG 9000]

OK so I've done this - it's ugly but it gets the job done :-


in My product xmlpackage I call a new xmlpackage like so :-

<xsl:value-of select="aspdnsf:XmlPackage('xmltest2.xml.config', concat('related=', $RelatedProducts, '&amp;cat1=', $CAT))" disable-output-escaping="yes" /> 

$RelatedProducts is the value of the RelatedProducts field
&
$CAT is the Category we're viewing the product in.


The new xmlpackage looks like this :-

<package version="2.1" displayname="BFG merge related prods" debug="false" includeentityhelper="false" allowengine="true">

<query name="Related" rowElementName="Products">
<sql>
<![CDATA[

DECLARE @BFG varchar(600)

SET @BFG = 
    'SELECT TOP 3 ProductID FROM Product WHERE ProductID IN (' + @related + ') AND Deleted = 0
    and Published = 1 ORDER BY newid()'

    EXEC(@BFG )

]]>
</sql>
        <queryparam paramname="@related" paramtype="runtime" requestparamname="related" sqlDataType="varchar" defvalue="0" validationpattern="" />
</query>

    <query name="CategoryRandom" rowElementName="Products">
        <sql>
            <![CDATA[
SELECT TOP 3
    p.ProductID
FROM Product p 
    join [dbo].ProductCategory pc with (nolock) on pc.productid = p.productid
WHERE p.Deleted = 0
    and p.Published = 1 and pc.CategoryID = @cat1 
ORDER BY newid()
            ]]>
        </sql>
        <queryparam paramname="@cat1" paramtype="runtime"   requestparamname="cat1" sqlDataType="int" defvalue="0" validationpattern="" />
    </query>


<PackageTransform>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:aspdnsf="urn:aspdnsf" exclude-result-prefixes="aspdnsf">
<xsl:output method="xml" omit-xml-declaration="yes" />

<xsl:param name="LocaleSetting" select="/root/Runtime/LocaleSetting" />


<xsl:template match="/">
<xsl:param name="related"><xsl:apply-templates select="/root/Related/Products" /><xsl:if test="/root/Related/Products">,</xsl:if><xsl:apply-templates select="/root/CategoryRandom/Products" /></xsl:param>
<xsl:value-of select="aspdnsf:XmlPackage('three.random-new.xml.config', concat('related=', $related))" disable-output-escaping="yes" />
</xsl:template>


<xsl:template match="/root/Related/Products"><xsl:value-of select="concat(., substring(',', 1 div not(position()=last())))"/></xsl:template>
<xsl:template match="/root/CategoryRandom/Products"><xsl:value-of select="concat(., substring(',', 1 div not(position()=last())))"/></xsl:template>

</xsl:stylesheet>
</PackageTransform>
</package> 

You can see that I then call three.random-new.xml.config which pulls the top 3 like this :-

<sql>
<![CDATA[

DECLARE @BFG varchar(600)

SET @BFG = 
    'SELECT DISTINCT TOP 3 p.ProductID, pv.Inventory FROM Product p LEFT JOIN ProductVariant pv on p.ProductID = pv.ProductID WHERE p.ProductID IN (' + @related + ') AND p.Published = 1 AND pv.Inventory > 0 '

    EXEC(@BFG )

]]>
</sql> 

TTFN

BFG