cte 或者with语句的查询结果 如何在自定义函数中返回

[sk9979]

USE [HNMESDB]
GO

/****** Object: UserDefinedFunction [dbo].[f_PL_GetSectionID] Script Date: 2014-12-24 9:04:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create FUNCTION [dbo].f_PL_GetSectionID(@father_id varchar(36))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''

with subqry(equipment_id,FATHER_ID,level) as (
select equipment_id,FATHER_ID,1 as level from V_APS_EQUIPMENT_TEMP where equipment_id=@father_id
union all
select a.equipment_id,a.FATHER_ID,level+1
from V_APS_EQUIPMENT_TEMP as a,subqry
where a.equipment_id = subqry.FATHER_ID
)
-- @str= SELECT equipment_id+'|' FROM subqry ORDER BY LEVEL desc FOR XML PATH('')

SELECT equipment_id+'|' FROM subqry ORDER BY LEVEL desc FOR XML PATH('')
RETURN @str
--STUFF(@str, 1, 1, '')
END
GO

[Tiger_Zhao]

;with subqry(equipment_id,FATHER_ID,level) as (
    ...
)
,t (str) as (
    SELECT equipment_id+'|'  FROM subqry ORDER BY LEVEL desc FOR XML PATH('')
)
SELECT @str = str FROM t
RETURN @str

[sk9979]

不知道如何返回SELECT equipment_id+'|' FROM subqry ORDER BY LEVEL desc FOR XML PATH('') 这个拼接后的字符串结果