USE [ArenaDB]
GO
/****** Object:  StoredProcedure [dbo].[cust_CCIW_get_family_data]    Script Date: 9/8/2020 11:31:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cust_CCIW_get_family_data]
@OrganizationId INT = 1, -- to be sent from HTML From SPROC Module
@SelectedPerson INT -- to be sent from the HTML From SPROC Module
AS
DECLARE
@html Varchar(MAX),
@CurrentDateTime DATETIME = GETDATE()

 

SELECT @html = COALESCE(@html + '<input type="hidden" id="GUID' + LOWER(person_guid) + '" age_value="' + Age + '" grade="' + Grade + '">', '<input type="hidden" id="GUID' + LOWER(person_guid) + '" age_value="' + Age + '" grade="' + Grade + '">')
FROM(
SELECT CP.person_id,
    FM.family_id,
    [person_guid] = CONVERT(char(36),CP2.guid),
    CP2.birth_date,
    [Grade] = CONVERT(Varchar(2), dbo.core_grade(@CurrentDateTime, CP2.graduation_date)),
    [Age] = CONVERT(Varchar(3),dbo.fn_Age(CP2.birth_date, @CurrentDateTime))
FROM core_person CP
LEFT JOIN core_family_member FM on CP.person_id = FM.person_id
LEFT JOIN core_family_member FM2 on FM.family_id = FM2.family_id
LEFT JOIN core_person CP2 on CP2.person_id = FM2.person_id
WHERE CP.person_id = @SelectedPerson
)
AS TEMP

 

SELECT @html AS html