SQL-PRV-02 Provider collection filtered by tax-ID list¶

Retrieves the set of PROVIDER records whose primary tax ID appears in a caller-supplied list. The IN list is built by direct string concatenation (String.Format("'{0}',", taxId)) rather than bind parameters.

Classification¶

Field	Value
SQL ID	SQL-PRV-02
Operation type	Read
Execution method	Inline SQL (BOF criteria constant, string-concatenated predicate)
Database	Oracle
Schema	`[unqualified]**`
Command type	Text
Business object	`clsProviderCollection`
Criteria property	`RetrieveSQL`
Lifecycle method	`BeforeRetrieve` / `AfterRetrieve`

Tables¶

Table	Access	Columns used
PROVIDER	SELECT	`PRIMARY_TAX_ID`, `PROVIDER_SEQ_NUM`, `UPDATE_DATE`, `UPDATE_NUM`, `UPDATE_USER`
PROVIDER_TAX_ID	JOIN (LEFT OUTER)	`TAX_ID`, `TAX_ID_DISPLAY_DESCR`, `REGISTERED_NAME`

Sample SQL¶

Security note: the IN list is produced by String.Format("'{0}',", taxId) and concatenated directly into the statement; no bind parameters are used (BR-PRV-003).

-- Provider collection filtered by a tax-ID list
SELECT
  PRV.PRIMARY_TAX_ID        AS PrimaryTaxID,
  PRV.PROVIDER_SEQ_NUM      AS ProviderID,
  PRV.UPDATE_DATE           AS UpdateDate,
  PRV.UPDATE_NUM            AS UpdateNum,
  PRV.UPDATE_USER           AS UpdateUser,
  TAX.TAX_ID_DISPLAY_DESCR  AS FormattedPrimaryTaxID,
  TAX.REGISTERED_NAME       AS RegisteredName
FROM PROVIDER PRV
  LEFT OUTER JOIN PROVIDER_TAX_ID TAX
    ON TAX.TAX_ID = PRV.PRIMARY_TAX_ID
WHERE prv.primary_tax_id IN ('123456789', '987654321')

Operations and processes¶

Retrieve provider set

Business rules¶

BR-PRV-003: Unparameterized provider collection SQL

Sources¶

SAIF.Business.View.Provider/clsProviderCollection.vb