USE [HNMESDB]
GO/****** Object: UserDefinedFunction [dbo].[f_PL_GetSectionID] Script Date: 2014-12-24 9:04:18 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOcreate 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
GO/****** Object: UserDefinedFunction [dbo].[f_PL_GetSectionID] Script Date: 2014-12-24 9:04:18 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOcreate 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
这个拼接后的字符串结果
...
)
,t (str) as (
SELECT equipment_id+'|' FROM subqry ORDER BY LEVEL desc FOR XML PATH('')
)
SELECT @str = str FROM t
RETURN @str