Review and try to interpret/explain the following stored procedures.

BEGIN TRY BEGIN TRANSACTION; --The family member must be associated with a ClientPersonID or we bail. IF @ClientPersonID = 0 OR @ClientPersonID IS NULL BEGIN SET @Success =0; SET @ErrorStatus = '-17500 ClientPersonID cannot be null or 0'; RAISERROR(@ErrorStatus,16,1); END SELECT @TempClientPersonID = @ClientPersonID FROM Person.Person WHERE PersonID = @ClientPersonID IF @TempClientPersonID = 0 OR @TempClientPersonID IS NULL--the client must exist to have a family member! BEGIN SET @Success=0; SET @ErrorStatus = '-17501 ClientPersonID does not exist in person'; RAISERROR (@ErrorStatus,16,1); END --Take the return value and throw error if 0 because we cannot proceed of we don't know --which set of sps to call. SET @ValidSource = DBO.ValidateDataSource(@RecordSource); IF @ValidSource =0 BEGIN SET @Success =0; SELECT @ErrorStatus = '-17100 Invalid Record source'; RAISERROR(@ErrorStatus,16,1); --Raise Error Here because the source is unknown. END --Insert into Person and take the new personID for further use. BEGIN EXEC DBO.pInsertPerson @Title,@FirstName,@MiddleName,@LastName,@Suffix, @DateOfBirth,@EmailAddress,@EmailContactPreference,@ModifiedBy,@NewPersonID Output,@Succ Output, @ErrorMsg Output; SET @NewPersonIDout = @NewPersonID; IF @Succ =0 -- Failure! BEGIN SET @Success = @Succ; SELECT @ErrorStatus = '-17200 Sub Pro Fail'+ @ErrorMsg; RAISERROR(@ErrorStatus,16,1); END SET @Success =1; SET @ErrorStatus=''; END --IF the source is from Food Shelf we cannot assume that we will need to call the address or phone SPs because they --are not required to take that information. --IF we have a good PersonID returned, we continue to Address --The address should be the same as that of the client, but we will add it anyway because this person --may eventually become a client and we will already have an AddressID that we can use and modify the location.

IF @NewPersonID > 0 AND @NewPersonID IS NOT NULL BEGIN IF ((@RecordSource <> 'FOOD') AND (LTRIM(RTRIM(@Address1)) ='') OR (LTRIM(RTRIM(@Address1)) IS NULL) )--This is a problem because address is required BEGIN SET @Succ =0; SET @ErrorStatus ='-17300 Address Required for Non foodshelf intake.'; RAISERROR(@ErrorStatus,16,1); END IF ((LTRIM(RTRIM(@Address1)) <> '') AND (@Address1 IS NOT NULL)) OR @RecordSource = 'FOOD' BEGIN EXEC DBO.pInsertAddress @NEWPersonID, @Address1,@Address2, @City,@County,@State,@PostalCOde,@AddressTypeID,@ModifiedBy, @NewAddressID Output,@Succ Output, @ErrorMsg Output ; IF @Succ =0 --Failed, Rollback! BEGIN SET @Success =0; SELECT @ErrorStatus = '-17200 Sub proc call returned error' + @ErrorMsg; RAISERROR (@ErrorSTatus, 16,1); END END

--Insert Phone Number --We allow phone number since they may have a cell. BEGIN IF ((@RecordSource <> 'FOOD') AND (LTRIM(RTRIM(@PhoneNumber)) ='') OR (LTRIM(RTRIM(@PhoneNumber)) IS NULL) )--This is a problem because Phone Number is required BEGIN SET @Succ =0; SET @ErrorStatus ='-17400 Phone Number Required for Non foodshelf intake.'; RAISERROR(@ErrorStatus,16,1); END IF (LTRIM(RTRIM(@PhoneNumber))<>'' AND @PhoneNumber IS NOT NULL) BEGIN EXEC DBO.pInsertPhone @NewPersonID,@PhoneNumber, @PhoneExtension,@PhoneTypeID,@ModifiedBy,@NewPhoneID output, @Succ output, @ErrorMsg output ; END IF @Succ =0 BEGIN SET @Success =0; SELECT @ErrorStatus = '-13200 Sub proc Phone returned fail' + @ErrorMsg; RAISERROR(@ErrorStatus, 16,1); END END --Insert Demographics BEGIN EXEC dbo.pInsertDemographics @NewPersonID,@EducationCategoryID ,@HousingStatus,@NumberInHousehold,@Gender,@Race,@CountryOfOrigin,@Ethnicity,@Disability,@CSFP,@Veteran,@HealthInsurance,@FoodStamps,@HomeboundDelivery,@ModifiedBy,@NewDemographicsID OUTPUT,@Succ OUTPUT, @ErrorMsg OUTPUT; IF @Succ = 0 BEGIN SET @Success =0; SELECT @ErrorStatus ='-13200 Sub proc Demographics returned fail' + @ErrorMsg; RAISERROR(@ErrorStatus,16,1); END END --Insert Client Notes if they exist. This insert procedure is for the client, so we do not have --A value for HouseholdMemberPersonID so we hard code it to 0. IF LTRIM(RTRIM(@notes))<>'' AND @Notes IS NOT NULL BEGIN EXEC DBO.pInsertHouseholdNotes @ClientPersonID, @NewPersonID,@Notes,@ModifiedBy,@NewClientNotesID OUTPUT,@Succ OUTPUT,@ErrorMsg OUTPUT; IF @Succ =0 BEGIN SET @Success =0 SET @ErrorStatus = '-17001 Insert Failed Notes' + @ErrorMsg; RAISERROR(@ErrorStatus,16,1); END END --We have everything except for the relationship to the client, so we do that last. EXEC DBO.pInsertFamilyRelationship @NewPersonIDout, @ClientPersonID,@RelationshipID,@RelationshipDescription,@ModifiedBy, @NewFamilyRelationshipID OUTPUT, @Succ OUTPUT, @ErrorMsg OUTPUT; IF @Succ =0 BEGIN SET @Success =0; SET @ErrorStatus ='-17001 Insert Failed Family Relationship'+ @ErrorMsg RAISERROR(@ErrorStatus,16,1); END

--We are using nested transactions to make sure one exists in case --of a rollback in subordinate Stored Procedure IF @@TRANCOUNT > 0 BEGIN SET @Success =1; SET @ErrorStatus =''; COMMIT TRANSACTION; END END END TRY BEGIN CATCH IF @@TRANCOUNT >0 BEGIN SET @Success =0 SET @ErrorStatus =@ErrorStatus + ' ' +@@Error Set @ErrorStatus = @ErrorStatus + 'Failure in pInsertClientFamilyRecordWithAddress' ROLLBACK TRANSACTION; RAISERROR(@ErrorStatus,16,1); END END CATCH