There has been far too many times that I’ve had to go back and recompile a SQL integration library, simply because I needed to compare a different column than the original, or filter out some unwanted results.

As it turns out, there’s a fairly straightforward method of supplying SQL integration components with a full condition string (or strings), immensely increasing the usability of a single component.

Typically, a SQL component is built thusly:

But now, what if during further development, it turns out that not only does the CatalogItemName need to be referenced, but I also need to check for IsEnabled to only return enabled rows?  I could always use a workflow Configurable Collection Filter to filter out the rows that IsEnabled = False; however, if I had filtered out the unwanted rows in the SQL query,  further computing cycles doesn’t have to be afforded a filter model in Workflow.

Instead, I’m going to adjust the SQL query in the integration wizard.

In the conditions section of my SQL script, I’ve hardcoded where 1=1 so that any input can disregard whether a “where” or “and” is needed, and always use “and”.

After completing the wizard for this component and compiling/recompiling, I’ll put the column names into the “Usage” field (visible when the component is selected) so that when configuring the component in the Designer, I have a quick and easy column reference for my input.

Now that the integration library is buttoned-up, I’ll load it into my project for use.  Notice that the initial value for the condition string input is set to –, which should work fine as is.

Also note the “Usage” field in the component’s Help menu:

Now to supply the input merge for the component’s _querystring parameter.

When using the “like” operator with data variables, it’s sometimes helpful to swap the “Not Found” value for a “%” so you can pass a blank value in and get results as if a “%” was passed in.

It should probably be noted as well that TextBox components are preconfigured to regex-scrape any user input.  As always, processes should be vetted and tested for vulnerabilities, but this feature provides a helpful baseline for sanitizing inputs.

Using this method, I’m able to reuse a single query component for fetching data from the table.