|
|
Error Message
Server: Msg 240, Level 16, State 2, Line 1
Types don't match between the anchor and the recursive
part in column "<Column Name>" of recursive
query "<Recursive Common-Table Expression Name>".
Causes
A common table expression (CTE), introduced in SQL Server 2005, can be thought of as a temporary result set that is defined with the execution scope of a single SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement.
|
A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. A CTE that references itself is referred to as a recursive common table expression.
The general syntax of a recursive common table expression is as follows:
WITH <CTE Name> [ ( [, ...n] ) ]
AS (
<CTE Query Definition – Anchor Member>
UNION ALL
<CTE Query Definition – Recursive Member>
)
SELECT * FROM <CTE Name>
One of the restrictions when defining a recursive common table expression is that the data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member. Otherwise, as the message suggests, this error will be generated.
To illustrate on how this error message may be encountered, here’s a script that demonstrates it:
CREATE TABLE [dbo].[Folder] (
[FolderID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
[FolderName] VARCHAR(100) NOT NULL,
[ParentFolderID] INT NULL REFERENCES [dbo].[Folder]( [FolderID] )
)
INSERT INTO [dbo].[Folder] ( [FolderName] )
VALUES ( 'Inetpub' )
INSERT INTO [dbo].[Folder] ( [FolderName] )
VALUES ( 'Program Files' )
INSERT INTO [dbo].[Folder] ( [FolderName] )
VALUES ( 'Windows' )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'wwwroot', 1 )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'Microsoft Office', 2 )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'Microsoft SQL Server', 2 )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'assembly', 3 )
INSERT INTO [dbo].[Folder] ( [FolderName], [ParentFolderID] )
VALUES ( 'system32', 3 )
WITH FullFolder ( [FolderID], [FolderName], [ParentFolderName], [FullFolderPath] ) AS (
SELECT [FolderID], [FolderName], '' AS [ParentFolderName], '\' + [FolderName]
FROM [dbo].[Folder]
WHERE [ParentFolderID] IS NULL
UNION ALL
SELECT A.[FolderID], A.[FolderName],
ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName],
ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName] + '\' + A.[FolderName]
FROM [dbo].[Folder] A INNER JOIN FullFolder B
ON A.[ParentFolderID] = B.[FolderID]
)
SELECT * FROM FullFolder
ORDER BY ParentFolderName, FullFolderPath
The SELECT statement part of the script will generate the following errors:
Msg 240, Level 16, State 1, Line 2
Types don't match between the anchor and the recursive part in column "ParentFolderName"
of recursive query "FullFolder".
Msg 240, Level 16, State 1, Line 2
Types don't match between the anchor and the recursive part in column "FullFolderPath"
of recursive query "FullFolder".
The recursive common-table expression (CTE) returns all folders defined in the [dbo].[Folder] table together with the name of the parent folder (if it exists) as well as the full path of the current folder.
Based on the error messages generated, 2 columns in the recursive CTE have mismatched data types and/or data type lengths. Although both the [ParentFolderName] and [FullFolderPath] columns in the first SELECT statement, the anchor member, as well as in the second SELECT statement, the recursive member, are of VARCHAR data types, their lengths differ.
In the anchor member, the ParentFolderName will have a length of 1 character for the empty string and the FullFolderPath will have a length of 101 characters (length of [FolderName] column (100) plus length of '\' (1)). However, in the recursive member, the ParentFolderName will have a length of 102 while the FullFolderPath will have a length of 204.
Solution / Work Around:
As the message suggests, to avoid this error from happening, make sure that the data type and data length of all columns in the anchor member of a recursive common-table expression (CTE) is the same as the data type and data length of the corresponding columns in the recursive member of the recursive CTE.
The easiest way to ensure this is to convert the columns into a common data type and length. In the recursive CTE example earlier, converting both columns causing the error from both the anchor member and the recursive member into VARCHAR(8000) will work around this issue, as can be seen from the following updated script:
WITH FullFolder ( [FolderID], [FolderName], [ParentFolderName], [FullFolderPath] ) AS (
SELECT [FolderID], [FolderName], CAST('' AS VARCHAR(8000)) AS [ParentFolderName],
CAST('\' + [FolderName] AS VARCHAR(8000))
FROM [dbo].[Folder]
WHERE [ParentFolderID] IS NULL
UNION ALL
SELECT A.[FolderID], A.[FolderName],
CAST(ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName] AS VARCHAR(8000)),
CAST(ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName] + '\' +
A.[FolderName] AS VARCHAR(8000))
FROM [dbo].[Folder] A INNER JOIN FullFolder B
ON A.[ParentFolderID] = B.[FolderID]
)
SELECT * FROM FullFolder
ORDER BY ParentFolderName, FullFolderPath
The result of this recursive CTE is as follows:
FolderID FolderName ParentFolderName FullFolderPath
---------- ----------------------- ----------------- ---------------
1 Inetpub \Inetpub
2 Program Files \Program Files
3 Windows \Windows
4 wwwroot \Inetpub \Inetpub\wwwroot
5 Microsoft Office \Program Files \Program Files\Microsoft Office
6 Microsoft SQL Server \Program Files \Program Files\Microsoft SQL Server
7 assembly \Windows \Windows\assembly
8 system32 \Windows \Windows\system32
Alternatively, instead of converting the columns on both the anchor member and the recursive member of the recursive CTE into VARCHAR(8000), the columns in the anchor member can be converted into VARCHAR(MAX) and doing nothing to the columns in the recursive member:
WITH FullFolder ( [FolderID], [FolderName], [ParentFolderName], [FullFolderPath] ) AS (
SELECT [FolderID], [FolderName], CAST('' AS VARCHAR(MAX)) AS [ParentFolderName],
CAST('\' + [FolderName] AS VARCHAR(MAX))
FROM [dbo].[Folder]
WHERE [ParentFolderID] IS NULL
UNION ALL
SELECT A.[FolderID], A.[FolderName],
ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName],
ISNULL(B.[ParentFolderName], '') + '\' + B.[FolderName] + '\' + A.[FolderName]
FROM [dbo].[Folder] A INNER JOIN FullFolder B
ON A.[ParentFolderID] = B.[FolderID]
)
SELECT * FROM FullFolder
ORDER BY ParentFolderName, FullFolderPath
|