>Hi All,
>
>I have to admit I am weak on this topic and would like to get better. I need a stored procedure where I can retrieve a record from a single table but the parameter passed in could be a match to a column in this table or it could be found in another. Here is the table structure.
>
>

>*** Household ***
>HouseholdID (UniqueID)
>LocationID (UniqueID)
>ApplicantID (UniqueID)  -> 1-1 to Persons
>
>*** HouseholdMembers ***
>HouseHoldID  (UniqueID)         -> HouseHold
>PersonId  (UniqueID)              -> Person
>RelationshipRoleID (UniqueID)  ->RelationshipRole
>DateAddedToHousehold (DateTime)
>

>
>What I need is to obtain the single row from Household and pass in a PersonID. The PersonID may be found in household in the ApplicantID column but if not, then it would be found in the HouseholdMembers table and would give the HouseholdID value there to get the row from.
>
>Any help appreciated to get me pointed to creating this stored procedure. Thanks
>Tim

I'd say, if the design requires this complicated algorithm, it may be not a good design.

Anyway, here is a possible solution

select HouseHoldID, ApplicantID from HouseHold where ApplicantID = @PersonID
UNION  -- UNION removes duplicates
select HouseHoldID, PersonID from HouseHoldMembers where PersonID = @PersonID