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 (SELECTProductCategoryIDFROM Tidebuy_SCM_Test.dbo.xxxx_xxxx parent(NOLOCK) WHERE [email protected]UNION ALLSELECTchild.ProductCategoryIDFROM 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    ENDGO

　　

SQL Server 【CTE + FOR XML PATH】使用筆記~