SQL Server 【CTE + FOR XML PATH】使用笔记~

CREATE
 FUNCTION [dbo].[Getxxxxxxxxx]
    (
      @productCategoryId INT,
      @SplitChar varchar
    )
RETURNS NVARCHAR(MAX)
AS
    BEGIN
		
        DECLARE @returnProductCategory NVARCHAR(MAX)=N‘‘

		/*-- 注意，CTE 表达式前面必须要用分号“;”把其代码分开,建议在WITH前面加分号“;” --*/
		;WITH cte AS (
			SELECT
				ProductCategoryID
			FROM Tidebuy_SCM_Test.dbo.xxxx_xxxx parent(NOLOCK) WHERE ProductCategoryID=@productCategoryId
	
			UNION ALL

			SELECT
				child.ProductCategoryID
			FROM xxx_xxx child(NOLOCK) INNER JOIN cte ON child.ParentID=cte.ProductCategoryID
		)

		SELECT @returnProductCategory = (
			SELECT  (
				SELECT   
					CAST(cte.ProductCategoryID AS NVARCHAR(10))+‘,‘
				FROM  cte FOR XML PATH(‘‘)
			) AS temp 
		)

        RETURN @returnProductCategory
    END
GO