Parameter Substitution

posted Mar 23, 2012, 11:43 AM by Eric Patrick
It is often useful to use the SQL IN clause to apply operations. The {Where.IdList} Sql Pattern provides a build-in pattern for building such clauses when applying an operation to multiple rows. For example:
  • In a search or list view of the Smart Worklist Member table, one can check a bunch of 'ID' checkboxes, and invoke the 'Cancel' command
  • In a search or list view of the Contact table, one can check a bunch of 'ID' checkboxes, and invoke the 'Change Status' command
However, the {Where.IdList} pattern only applies to IDs for the subject table.

The Valuation Hub must represent the contractual relationship between 'Service Providers' (BPO companies) and their 'Clients'. The new Contract and ContractProduct classes handle this mapping. For example:
  • A Contract may be between ORT (Contract.VendorID) and Chase (Contract.ClientID)
  • A ContractProduct row may be created for the following Process Templates: 'BPO - Interior', 'BPO - Exterior', and 'Appraisal', 
  • The Contract may omit a ContractProduct row for the Process Template 'Reconciled Market Value', meaning that Chase does not order such products from ORT
When designing the New Contract page, it is convenient to present the user with the basic Contract fields, as well as a list of Process Templates, allowing the user to select the products being covered under the contract from the same screen.

The SQL statement in the Contract.config for this needs to be something like:

<Statement Name="New" Query="
INSERT INTO {Table} ({Select.Insert}) VALUES ({From.Insert}) 
INSERT INTO ContractProduct (ContractProduct, ContractID, ProcessTemplateID, CreatedDate, CreatedPersonID)
SELECT ProcessTemplate, SCOPE_IDENTITY(), ProcessTemplateID, @CreatedDate, @CreatedPersonID)
FROM ProcessTemplate
WHERE ProcessTemplateID IN ({A list of Process Template IDs here would be nice})
">

The {Where.IdList} pattern does not work in this case, because it's engineered to expect Contract IDs, not ProcessTemplateIDs.

For this situation, we can use the {Param.*} pattern, to wit:

<Statement Name="New" Query="
INSERT INTO {Table} ({Select.Insert}) VALUES ({From.Insert}) 
INSERT INTO ContractProduct (ContractProduct, ContractID, ProcessTemplateID, CreatedDate, CreatedPersonID)
SELECT ProcessTemplate, SCOPE_IDENTITY(), ProcessTemplateID, @CreatedDate, @CreatedPersonID)
FROM ProcessTemplate
WHERE ProcessTemplateID IN ({Param.ProcessTemplateID})
">
<Parameters>
<Parameter Name="ProcessTemplateID" DbType="String" Value="0"/>
</Parameters>
</Statement>

The {Param.ProcessTemplateID} will be replaced by a ProcessTemplateID parameter. Assuming the New Contract page has a bunch of checkboxes named 'ProcessTemplateID', the data being send to the server will look like:

Contract.ashx/New?Contract=My New Contract&...&ProcessTemplateID=1&ProcessTemplateID=6&...

translating to a ProcessTemplateID parameter like this:

ProcessTemplateID: '1,6'

translating to a SQL snippet like this:

WHERE ProcessTemplateID IN (1, 6)

Note that the Statement in the configuration file has a default parameter value of 0. If the user checks no checkboxes, the SQL IN clause will still be syntactically valid, and simply result in no ContractProduct rows being added since there are no ProcessTemplate rows with an ID of 0.
Comments