Thread: Dynamic Where clause in XML packages

Is there a way to build a dynamic where clause (add on to the where statement) in the SQL part of the XML packages?

So I could have something like:

<query name="Products" rowElementName="Product">
<sql>
<![CDATA[
Select * from Products where Sku like '01%'
]]>

BUT BY ABLE TO ADD and Name like to it like below-->

<query name="Products" rowElementName="Product">
<sql>
<![CDATA[
Select * from Products where Sku like '01%' and Name like 'Math%'
]]>


I'm trying to let the user select from three different dropdownlist to select a filter on their product searches and, based off their selections, I want to be able to query the product table in an XMLpackage with the different options (dynamic where) the user supplied.

Thanks for any ideas.

Hi

I'm not aware of any way to add dynamic generation to the SQL used in the XML <query> block.

Probably the easiest solution would be to write a custom database procedure, pass your filters to the procedure and build the where clause within it returning the results to the XML template.

Alternatively, if you really need to use XML, you might be able to use one SQL statement with a where clause containing all your filter items and use SQL variables to pass in the correct values.

Something like this [with a bit more work]:

Select * from Products where Sku like @skuFilter and Name like @nameFilter

<queryparam paramname="@skuFilter" paramtype="request" requestparamname="skuFilter" sqlDataType="varchar" defvalue="%"
validationpattern="" />
<queryparam paramname="@nameFilter" paramtype="request" requestparamname="nameFilter" sqlDataType="varchar" defvalue="%"
validationpattern="" />

Thanks-

I was thinking about going with the SP route ... but I might try the variable way first to see if it can handle what I need to do.