CTE and data type

I have a recursive CTE and within it is a concatenated string field. It fails with this message

Msg 240, Level 16, State 1, Procedure spGetBill, Line 14

Types don't match between the anchor and the recursive part in column "BID" of recursive query "ExplodedParts".

Here is the T-Sql:

WITH ExplodedParts (PID, CID, BID, PBOMID)

AS (SELECT ParentID, ChildID, convert(varchar,ParentID) as BID, PBOMID

FROM dbo.vwLatestPBOMIDs

WHERE ParentID=@.ParentID

UNION ALL

SELECT NextLevel.ParentID, NextLevel.ChildID, EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID, NextLevel.PBOMID

FROM dbo.vwLatestPBOMIDs AS NextLevel INNER JOIN

ExplodedParts AS EP ON NextLevel.ParentID = EP.CID)

SELECT PID, CID, BID, PBOMId

FROM ExplodedParts AS ExplodedParts_1

ORDER BY PID

The offending Sql is "EP.PID + '.' + convert(varchar,NextLevel.ParentID) AS BID", which if I change to "convert(varchar,NextLevel.ParentID) AS BID" runs fine. I have tried using the Convert fn in the source view, no difference.

Does anyone have a clue why this is causing the datatype issue?

Any help is appreciated.

In anchor part try changing:

convert(varchar,ParentID) as BID

to:

convert(varchar(max),ParentID) as BID

|||

Change:

convert(varchar, ParentID)

to:

cast(ParentID as varchar(255)) -- or whatever maximum length you need

And change:

EP.PID + '.' + convert(varchar,NextLevel.ParentID)

to:

cast(EP.PID + '.' + convert(varchar,NextLevel.ParentID) as varchar(255))

By default if you don't specify the length for varchar/char/nchar/nvarchar/varbinary/binary in convert, the resulting length will be 30. So your anchor member column is of length 30 whereas the recursive member is 30 + 1 + length(PID). It is good rule to always use explicit cast around expressions so that you know the resulting type and this is more important to follow with recursive CTEs.

|||Thank you, that solved the issue.