GetClientRels Procedure

CREATE PROCEDURE GetClientRels
(
/*
relationship key for @RelType
35 = head of household
36 = Spouse
37 = Child
38 = Parent
39 = Other
other values will show all relationships
*/
@TheClient INT,
@RelType INT NULL
)

AS
IF @RelType BETWEEN 35 AND 39 --This is what executes if a CORRECT relationship code is entered
    BEGIN

    WITH CLIENT AS
        (
        SELECT rel.ClientPersonID, rel.RelationDescription, rel.PersonID FROM person.FamilyRelationships AS rel
        WHERE rel.ClientPersonID = @TheClient
        ),
        RELS AS
        (
        SELECT rel.PersonID, rel.RelationshipID, pers.FirstName, pers.LastName FROM person.FamilyRelationships AS rel
        INNER JOIN person.person AS pers ON pers.PersonID = rel.PersonID
        WHERE rel.RelationshipID = @RelType
        )

    SELECT RELS.FirstName, RELS.LastName, CLIENT.RelationDescription FROM RELS
    JOIN CLIENT ON CLIENT.PersonID = RELS.PersonID

    END


ELSE --This executes if a relationship code is not entered

    BEGIN

    WITH CLIENT AS
        (
        SELECT rel.ClientPersonID, rel.RelationDescription, rel.PersonID FROM person.FamilyRelationships AS rel
        WHERE rel.ClientPersonID = @TheClient
        )

    SELECT person.person.FirstName, person.person.LastName, CLIENT.RelationDescription FROM person.Person
    INNER JOIN CLIENT ON CLIENT.ClientPersonID = person.person.PersonID
    WHERE CLIENT.ClientPersonID = @TheClient
    END