declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @companyName varchar(50) = ( select mfr_name from tblManufacturers where mfr_id = @ownerId ); ( select broker_name from tblBrokers where broker_id = @ownerId ); ''; with clientUsers as ( select fspro_userId, firstName, lastName, email, staffMember, firstName + ' ' + lastName as fullName, lastName + ', ' + firstName as lastNameFirstName, case when nullif( email, '' ) is null then 0 else 1 end hasEmail, loginEnabled, case when staffMember = 1 then 'staff' else 'nonStaff' end userType, ownerId userOwnerId, fsl_tablecode userOwnerType, @companyName userCompanyName from tbl_FSPro_Members with (nolock) where ownerID = @ownerId and fsl_tableCode = @ownerType and loginEnabled = ) , brokerUsers as ( select distinct m.fspro_userId, m.firstName, m.lastName, m.email, cast ( 0 as bit ) staffMember, m.firstName + ' ' + m.lastName as fullName, m.lastName + ', ' + m.firstName as lastNameFirstName, case when nullif( m.email, '' ) is null then 0 else 1 end hasEmail, m.loginEnabled, 'brokerStaff' userType, m.ownerId userOwnerId, m.fsl_tablecode userOwnerType, b.broker_name userCompanyName from tbl_SMA_BrokerOfficeLink bol with (nolock) inner join tblMfrBrokerLink ml with (nolock) on ml.MFR_ID = bol.MFR_ID and ml.BROKER_OFFICE_ID = bol.brokerOfficeId inner join tblBrokers b with (nolock) on b.BROKER_ID = bol.BrokerID inner join tblBrokerOffices bo with (nolock) on bo.BROKER_OFFICE_ID = bol.BrokerOfficeID inner join tbl_FSPro_Members m with (nolock) on m.ownerId = bol.brokerId and m.fsl_tablecode = 'BRO' left outer join tblBrokerOffices po with (nolock) on po.Broker_Office_ID = m.PrimaryOfficeID inner join tbl_ORG_AttributeLinks al with (nolock) on al.child_attrID = ml.BROKER_OFFICE_ID and al.mstr_attrTypeID = 34 and al.mstr_attrID = m.FSPro_UserID and al.child_attrTypeID = 27 left outer join tbl_sec_memgrplink mgl with (nolock) on mgl.FSPro_UserID = m.FSPro_UserID left outer join tbl_SEC_Groups grp with (nolock) on grp.GroupID = mgl.GroupID AND ((grp.GroupTypeCD = 'MRR') OR (grp.GroupTypeCD = 'MPR' AND mgl.PartnerID = ml.MFR_ID)) where bol.MFR_ID = @ownerId and m.staffMember = 1 and m.loginEnabled = ) , data as ( select * from clientUsers with (nolock) union all select * from brokerUsers with (nolock) ) #forJSON( 'data', attributes.datastore, 'fullName' )#