---
title: "cp_external_client_lookup stored procedure documentation"
summary: "Reference documentation for the SQL Server procedure that resolves an external client's e-mail address (legacy path)."
status: draft
owner: "SAIF Doc Gen"
last_reviewed: "2026-08-17"
source_schema: "dbo"
source_name: "cp_external_client_lookup"
source_type: "SQL_STORED_PROCEDURE"
oracle_status: "not-found"
sqlserver_status: "found"
sqlserver_database: "eisD0P11 (EIS_PROD catalog referenced in source)"
library: SAIF.Business.View.Policy
reference_type: package
---

# cp_external_client_lookup

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `dbo` |
| **Type** | Standalone SQL Server stored procedure |
| **Database** | SQL Server — BDS database `eisD0P11`, reading the `EIS_PROD.dbo.Net_Registrant` table via a three-part name |
| **Status** | ✅ Found in SQL Server (not found in Oracle — confirmed not an Oracle object) |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsTeamAssignment` |

This procedure was previously undocumented because Oracle was the only stored-procedure source retrievable during the original analysis. The `@`-prefixed parameter syntax and the separate data-portal instance (`inEmailAddressDPI`) correctly signalled a SQL Server target*; source retrieval via `legends sqlserver query --name cp_external_client_lookup --database Unknown` confirms the procedure and resolves the database as `eisD0P11`.

## Procedures / Methods

### cp_external_client_lookup

| Field | Value |
| --- | --- |
| **Returns** | Result set (top 1 row) from `Net_Registrant` |
| **Called by** | `clsTeamAssignment.InitializeEmailAddress` |
| **SQL document** | [SQL-024](../sql/SQL-024-external-client-lookup-legacy.md) |

**Description:** Looks up an external client's e-mail (mail ID) based on the client's user number and last name.

#### Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `@idNum` | Input | `char(100)` | 100 | User/mail identifier used to look up an external client's e-mail address. |
| `@clientLastName` | Input | `char(255)` | 255 | Last name derived from the assignment's full name, used as a secondary lookup key. |

## Business Rules

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| [PROC-RULE-022](../business-rules.md#proc-rule-022) | Match is a trimmed exact match on both mail identifier and last name | `RTRIM(reg.ID_NUM) = RTRIM(@idNum) AND RTRIM(reg.USER_LAST_NAM) = RTRIM(@clientLastName)` | Include the row as a candidate match |
| [PROC-RULE-023](../business-rules.md#proc-rule-023) | Only active, non-test registrant rows are eligible | `reg.ACTIVE_FL = 'Y' AND reg.TEST_USER_FL = 'N'` | Exclude inactive or test-user rows from the result |
| [PROC-RULE-024](../business-rules.md#proc-rule-024) | When multiple registrant rows match, the most recently updated row wins | `ORDER BY reg.UPDATE_DATE DESC` with `TOP 1` | Return only the single most-recently-updated matching row |

> These rules are also listed under `## Stored procedure business rules` in [business-rules.md](../business-rules.md).

## Operations

| Operation | Description | Trigger |
| --- | --- | --- |
| [PROC-OP-01](../operations-processes.md#proc-op-01) | Query `EIS_PROD.dbo.Net_Registrant` for a matching, active, non-test registrant by ID number and last name | Procedure invoked with `@idNum` and `@clientLastName` |
| [PROC-OP-02](../operations-processes.md#proc-op-02) | Return the most recently updated matching row (or none) to the .NET caller | Zero or more candidate rows found |

> These operations are also listed under `## Stored procedure operations` in [operations-processes.md](../operations-processes.md).

## Source Code

<details>
<summary>T-SQL stored procedure source</summary>

```sql
-- Actual T-SQL source retrieved from SQL Server (database eisD0P11) via Legends CLI
Create Procedure dbo.cp_external_client_lookup
--	LIST PARAMETERS
	@idNum char(100),
	@clientLastName char(255)
AS

/*--------------------------------------------------------------------------------
Procedure Name:	cp_external_client_lookup
Date Written:	01/08/2007
Author:		Jerry Todd (JERTOD)
Installation:	SAIF Corporation
Source File: 	cp_external_client_lookup.sql
Description:	This procedure looks up an external client's mailid based on the client's
		user number

		PARMS: client ID number and client Last Name
Modifications:
--------------------------------------------------------------------------------*/
set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


select top 1 *
from EIS_PROD.dbo.Net_Registrant reg
where
rtrim(reg.ID_NUM)=rtrim(@idNum)
and rtrim(reg.USER_LAST_NAM)=rtrim(@clientLastName)
and reg.ACTIVE_FL='Y'
AND reg.TEST_USER_FL='N'

order by reg.UPDATE_DATE desc


/* END THE PROCEDURE */

Return
```

</details>

## Notes
- The .NET caller reads the `EMAIL_ADDR` column from the returned row (`Reader.FirstOrDefault.GetString("EMAIL_ADDR")`); this column is confirmed to exist on `Net_Registrant` by the retrieved source.
- This is the legacy lookup path. The current replacement path is `InitializeEmailAddressUsingSystemUser`, which calls the confirmed Oracle function [sp73341_external_client_lookup](sp73341_external_client_lookup.md). Both paths remain live in source; this procedure is not dead code — it is reachable whenever `InitializeEmailAddress` is invoked directly.

## Related documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [Database tables](../database-tables.md)
- [SQL catalog](../sql/index.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
- SQL Server BDS database `eisD0P11`: `dbo.cp_external_client_lookup` (retrieved via Legends CLI `legends sqlserver query`)
- `Business/SAIF.Business.View.Policy/clsTeamAssignment.vb`
- [SQL-024: External client e-mail lookup (legacy path)](../sql/SQL-024-external-client-lookup-legacy.md)