今天执行一递归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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/81227/viewspace-743137/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/81227/viewspace-743137/