SQL Server处理ntext字段时的2个问题

1、如何去掉重复字段？
由于ntext类型的字段存储方式不通，SQL Server不能distinct，需要使用一个子查询来做。

CREATE PROCEDURE [dbo].[sp_Pts_GetRecordList]
@ProblemID int
AS
select a.*,
(select DesignContent from pts_lowlevelDesign where lowDesignId=a.recordId) as Content
from v_pts_records a
where problemid=@ProblemID
order by datecreated

其中视图v_pts_records中已经将大文本字段过滤掉了。

2、多个ntext字段如何合并？
因为ntext字段的存储不是ASCII码，不能在SQL语句中直接做合并，只能从程序中合并，参考如下代码，需要做3遍Case选择返回3个独立的ntext字段：

CREATE PROCEDURE [dbo].[sp_Pts_GetRecordList]
@ProblemID int
AS
select a.*,
(case a.RecordTypeName
    when ‘Low Level Design’  then (select LogicDesign from pts_lowlevelDesign where lowDesignId=a.recordId)
    when ‘High Level Design’ then (select DataModel from pts_HighlevelDesign where highDesignId=a.recordId)
    ELSE (select content from pts_records where recordid=a.recordId)
end
) content1,
(case a.RecordTypeName
    when ‘Low Level Design’  then (select UIDesign from pts_lowlevelDesign where lowDesignId=a.recordId)
    when ‘High Level Design’ then (select DesignContent from pts_HighlevelDesign where highDesignId=a.recordId)
    ELSE (select ” )
end) content2,
(case a.RecordTypeName
    when ‘Low Level Design’  then (select ServiceDesign from pts_lowlevelDesign where lowDesignId=a.recordId)
    when ‘High Level Design’ then (select ” )
    ELSE (select ”)
end) content3
from v_pts_records a
where problemid=@ProblemID
order by datecreated