向存储过程中的 case 语句添加'|'字符

Question

我在存储过程中有以下 case 语句：

Position    = case coalesce(teamMember.JobTitle, '')  when '' then '' else '<b>' + coalesce(teamMember.JobTitle, '') + '</b>' end
                                + isnull(teamMember.OrganizationNameLevel1, '')
                                + case when isnull(teamMember.OrganizationNameLevel2, '') <> isnull(teamMember.OrganizationNameLevel1, '') THEN ' | ' + isnull(teamMember.OrganizationNameLevel2, '') ELSE '' END
                                + case when isnull(teamMember.OrganizationNameLevel3, '') <> isnull(teamMember.OrganizationNameLevel2, '') THEN ' | ' + isnull(teamMember.OrganizationNameLevel3, '') ELSE '' END 
                                + case when isnull(teamMember.OrganizationNameLevel4, '') <> isnull(teamMember.OrganizationNameLevel3, '') THEN ' | ' + isnull(teamMember.OrganizationNameLevel4, '') ELSE '' END
                                + case when isnull(teamMember.OrganizationNameLevel5, '') <> isnull(teamMember.OrganizationNameLevel4, '') THEN ' | ' + isnull(teamMember.OrganizationNameLevel5, '') ELSE '' END
                                + case when isnull(teamMember.OrganizationNameLevel6, '') <> isnull(teamMember.OrganizationNameLevel5, '') THEN ' | ' + isnull(teamMember.OrganizationNameLevel6, '') ELSE '' END, 

它产生以下结果：

<b>APPS SYSTEMS ENGINEER</b>TECH & OPS | ENT INFO TECH | DEP & OPS TECH | HR SYS & ITECH | COMP & BEN APPS

我需要的是让它

显示的是：

**<b>APPS SYSTEMS ENGINEER</b> |** TECH & OPS | ENT INFO TECH | DEP & OPS TECH | HR SYS & ITECH | COMP & BEN APPS

Answer 1

它不应该像以下那样简单：

Position = case coalesce(teamMember.JobTitle, '')  when '' then '' else '<b>' + coalesce(teamMember.JobTitle, '') + '</b>' end
  + isnull(' | ' + teamMember.OrganizationNameLevel1, '')  --<-- add a Pipe here inside the ISNULL function 
  + case when isnull(teamMember.OrganizationNameLevel2, '') <> isnull(teamMember.OrganizationNameLevel1, '') THEN ' | ' + isnull(teamMember.OrganizationNameLevel2, '') ELSE '' END
  + .........