Limited_Ice

GetClientRels Procedure

Sep 16th, 2020
690
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE PROCEDURE GetClientRels
  2. (
  3. /*
  4. relationship key for @RelType
  5. 35 = head of household
  6. 36 = Spouse
  7. 37 = Child
  8. 38 = Parent
  9. 39 = Other
  10. other values will show all relationships
  11. */
  12. @TheClient INT,
  13. @RelType INT NULL
  14. )
  15.  
  16. AS
  17. IF @RelType BETWEEN 35 AND 39 --This is what executes if a CORRECT relationship code is entered
  18.     BEGIN
  19.    
  20.     WITH CLIENT AS
  21.         (
  22.         SELECT rel.ClientPersonID, rel.RelationDescription, rel.PersonID FROM person.FamilyRelationships AS rel
  23.         WHERE rel.ClientPersonID = @TheClient
  24.         ),
  25.         RELS AS
  26.         (
  27.         SELECT rel.PersonID, rel.RelationshipID, pers.FirstName, pers.LastName FROM person.FamilyRelationships AS rel
  28.         INNER JOIN person.person AS pers ON pers.PersonID = rel.PersonID
  29.         WHERE rel.RelationshipID = @RelType
  30.         )
  31.  
  32.     SELECT RELS.FirstName, RELS.LastName, CLIENT.RelationDescription FROM RELS
  33.     JOIN CLIENT ON CLIENT.PersonID = RELS.PersonID
  34.  
  35.     END
  36.    
  37.  
  38. ELSE --This executes if a relationship code is not entered
  39.  
  40.     BEGIN
  41.  
  42.     WITH CLIENT AS
  43.         (
  44.         SELECT rel.ClientPersonID, rel.RelationDescription, rel.PersonID FROM person.FamilyRelationships AS rel
  45.         WHERE rel.ClientPersonID = @TheClient
  46.         )
  47.  
  48.     SELECT person.person.FirstName, person.person.LastName, CLIENT.RelationDescription FROM person.Person
  49.     INNER JOIN CLIENT ON CLIENT.ClientPersonID = person.person.PersonID
  50.     WHERE CLIENT.ClientPersonID = @TheClient
  51.     END
RAW Paste Data