---
title: Provider search
summary: Dynamically searches provider locations across service type, MCO plan, business/person flag, tax ID, other identifier, provider name, address, and phone filters, unioning a registered-name match when a name is supplied.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-07-30
library: SAIF.Business.View.Provider
reference_type: package
operation_id: OP-VP-08
trigger: Caller populates clsProviderSearch.SearchCriteria and calls searchForProvider()
---

# Provider search

Provider search is a dedicated orchestrator (`clsProviderSearch`), distinct from the standard business-object/criteria retrieval pattern used elsewhere in this library. It builds its SQL entirely at runtime from a `StringBuilder`, adding a join and a bind parameter only for each search field the caller actually populated ([BR-003](../business-rules.md#br-003-provider-search-dynamic-join-filtering)). When the caller supplies a provider name, the query becomes a `UNION` of a name-based branch (matching last/business name) and a registered-name branch (matching the tax ID record's registered name), so a provider can be found by either its display name or its registered tax name.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-VP-08 |
| **Trigger** | Caller sets fields on `clsProviderSearch.SearchCriteria` (a nested `clsSearchCriteria` business object) and calls `searchForProvider()` |
| **Inputs** | `SearchCriteria.BusinessPerson` ("BUSINESS", "PERSON", or blank for both), `TaxId`, `OtherId`, `ServiceType`, `MCOName`, `ProviderName`, `ProviderType`, `Street`, `City`, `State`, `ZipCode`, `AreaCode`, `PhoneNum` — all optional |
| **Outputs** | `Providers` — a `clsProviders` collection of `clsSearchResults`, each carrying the provider location ID, primary-location flag, business flag, type description, resolved full name, sort name, physical address fields, and phone number, ordered by sort name; capped at 300 results |
| **Primary components** | `clsProviderSearch.searchForProvider`, `clsProviderSearch.FormatSortName`, `clsProviderSearch.PRVSearch`, `clsProviderSearch.PRVSearchRegName` |
| **Entry point** | `Business\SAIF.Business.View.Provider\clsProviderSearch.vb` |

## Steps

1. **Reset accumulated results.** `searchForProvider()` starts by replacing `m_Providers` with a fresh, empty `clsProviders` collection, discarding any prior search results before running the new search.
2. **Apply the business/person filter.** Based on `SearchCriteria.BusinessPerson`: `"BUSINESS"` adds `WHERE pl.BUSINESS_FLAG = :BusinessFlag` bound to `"Y"`; `"PERSON"` binds `"N"`; any other value starts the `WHERE` clause as `1 = 1` so every subsequent condition can be appended as an `AND` with no business-flag filter applied ([BR-003](../business-rules.md#br-003-provider-search-dynamic-join-filtering)).
3. **Add a tax ID join when supplied.** If `TaxId` is non-blank: join `provider_tax_id TIN` on the provider ID and bind `:TaxIDNumber` to the trimmed tax ID with dashes removed ([SQL-015](../sql/SQL-015-saif-business-view-provider.md), [PROVIDER_TAX_ID](../database-tables.md#provider_tax_id)) ([BR-003](../business-rules.md#br-003-provider-search-dynamic-join-filtering)).
4. **Add an other-identifier join when supplied.** If `OtherId` is non-blank: join `provider_identity ID` on the provider location ID and bind `:OtherID` to the upper-cased, trimmed value ([PROVIDER_IDENTITY](../database-tables.md#provider_identity)) ([BR-003](../business-rules.md#br-003-provider-search-dynamic-join-filtering)).
5. **Add a service-type join when supplied.** If `ServiceType` is non-blank: join `provider_service SVC` on the provider location ID, filtering the service code to the supplied value, and bind `:ServiceType` ([PROVIDER_SERVICE](../database-tables.md#provider_service)) ([BR-003](../business-rules.md#br-003-provider-search-dynamic-join-filtering)).
6. **Add an MCO plan join when supplied.** If `MCOName` is non-blank: join `mco_provider MP` on the provider location ID, filtering the MCO plan ID, and bind `:MCOPlan` ([MCO_PROVIDER](../database-tables.md#mco_provider)) ([BR-003](../business-rules.md#br-003-provider-search-dynamic-join-filtering)).
7. **Append name, type, address, and phone predicates when supplied.** For each of `ProviderName`, `ProviderType`, `Street`, `City`, `State`, `ZipCode`, `AreaCode`, and `PhoneNum` that is non-blank, append the matching `AND` predicate (and, when a provider name is supplied, the parallel predicate on the registered-name branch) and bind the corresponding parameter ([PROVIDER_LOCATION](../database-tables.md#provider_location), [PROVIDER_ADDRESS](../database-tables.md#provider_address)) ([BR-003](../business-rules.md#br-003-provider-search-dynamic-join-filtering)).
   - The provider-name predicate matches `LAST_NAME_UPPER_CASE` or `BUSINESS_NAME_UPPER_CASE` with a trailing-wildcard `LIKE`, so it is effectively a starts-with search on the upper-cased name.
8. **Assemble the final query.** The base name-search branch (`PRVSearch`) and, only when a provider name was supplied, the registered-name branch (`PRVSearchRegName`) joined by `UNION`, each with the same dynamically built join and `WHERE` clauses appended, then a fixed `ORDER BY fullNAME ASC` ([SQL-015](../sql/SQL-015-saif-business-view-provider.md), [PROVIDER_LOCATION](../database-tables.md#provider_location), [PROVIDER_TYPE_CODE](../database-tables.md#provider_type_code)).
9. **Execute and map results.** Each returned row is mapped into a `clsSearchResults` with the location ID, primary-location flag, business flag, type description, full name, a computed sort name, physical address line/city/state/zip, and phone number.
   - Sub-step: `FormatSortName` re-orders a "Last, First Middle" display name into "First Last, ..." for sorting purposes when the row is a person (not flagged as a business) and the name contains a comma; business names pass through unchanged.
10. **Cap the result set.** If more than 299 results have already been added, the mapping loop exits immediately (`Exit For`), leaving the result set capped at 300 rows and silently truncating any further matches.
11. **Sort the final result set.** `m_Providers.OrderBy(...)` sorts by the computed sort name — the return value of `OrderBy` is discarded (LINQ's `OrderBy` does not mutate the source collection in place), so this sort call has no observable effect on the returned `Providers` collection order.*

## Process flow

The following diagram visualizes this operation's control flow.

```mermaid
flowchart TD
	Start([searchForProvider called]) --> Reset[Reset Providers collection]
	Reset --> BizFlag{BusinessPerson filter?}
	BizFlag -->|Business| WhereBiz[WHERE BUSINESS_FLAG = Y]
	BizFlag -->|Person| WherePerson[WHERE BUSINESS_FLAG = N]
	BizFlag -->|Neither| WhereAll[WHERE 1 = 1]
	WhereBiz --> Joins
	WherePerson --> Joins
	WhereAll --> Joins
	Joins[Add joins only for supplied filters:<br>tax ID, other ID, service type, MCO plan] --> Preds[Append predicates only for supplied filters:<br>name, type, street, city, state, zip, area code, phone]
	Preds --> NameCheck{Provider name supplied?}
	NameCheck -->|Yes| Union[UNION base branch<br>with registered name branch]
	NameCheck -->|No| Single[Use base branch only]
	Union --> Exec[Execute assembled query,<br>ORDER BY fullNAME]
	Single --> Exec
	Exec --> Loop[Map each row to clsSearchResults;<br>compute sort name]
	Loop --> Cap{More than 299 results added?}
	Cap -->|Yes| Stop([Stop mapping; result set capped at 300])
	Cap -->|No| More{More rows?}
	More -->|Yes| Loop
	More -->|No| Done([Providers collection returned])
	Stop --> Done
```

## Failure and edge-case behavior

- The result set is silently capped at 300 rows; a search that matches more providers than that returns only the first 300 encountered, with no indication to the caller that results were truncated.
- The final `m_Providers.OrderBy(...)` call discards its return value, so it does not actually reorder the `Providers` collection; the effective result order is whatever `ORDER BY fullNAME ASC` produced from the database.*
- When no provider name is supplied, only the base name-search branch runs; a provider findable only by registered tax name (and not by last/business name) will not appear in a search that omits the name filter.
- The single-quote replacement on the provider name search value (`SearchName.Replace("'", "''")`) discards its result rather than reassigning it back to `SearchName`, so embedded single quotes are not actually escaped before being bound as a parameter value — though because the value is passed as a bind parameter rather than concatenated into the SQL text, this does not create a SQL-injection risk, only a functional no-op.*

## Related documentation

- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts.

## Sources

- `Business\SAIF.Business.View.Provider\clsProviderSearch.vb`