MS SQL Server的遞迴查詢(2)

今天執行一遞迴SQL，報型別不匹配錯誤：

With QueryName

AS (

    Select SiteID,SiteName,PSID,Path, 0 Level,

           '/'+ SiteName SitePath

      from SiteInfo

     where PSID is null

Union all

    Select a.SiteID,a.SiteName,a.PSID,a.Path, b.Level+1,

           b.SitePath+ '/'+a.SiteName SitePath

      from SiteInfo a, QueryName b

     where a.PSID=b.SiteID

) select * from QueryName

訊息240，級別16，狀態1，第1 行

在遞迴查詢"QueryName" 的列"SitePath" 中，定位點型別和遞迴部分的型別不匹配。

解決辦法：

1、對列做顯式型別轉換；

2、with中第一條select的欄位長度一定要足夠長。

With QueryName

AS (

    Select SiteID,SiteName,PSID,Path, 0 Level,

           CAST('/'+ SiteName as varchar(4000)) SitePath

      from SiteInfo where PSID is null

    Union all

    Select a.SiteID,a.SiteName,a.PSID,a.Path, b.Level+1,

           cast(b.SitePath+ '/'+a.SiteName as varchar(4000))

      from SiteInfo a, QueryName b

     where a.PSID=b.SiteID

) select * from QueryName order by SitePath