CTE遞迴查詢發生錨點與遞迴部分之間的類型不符

2023-08-21

很多情境會使用CTE來跑遞迴，最常的情境應該就是用CTE跑出階層效果。

而要順利使用遞迴會有一些限制，其中官方文件有寫到一個限制就是 -->

遞迴成員資料行的資料類型必須與錨點成員中對應資料行的資料類型相同。

以下是因為該限制而發生錯誤的Demo Code

declare @v_input_string varchar(1000)='A;B;C';
declare @v_delimiter char(1)=';';

with v_go_loop (output_value, list) as
(
	select 
		substring(@v_input_string, 1, charindex(@v_delimiter, @v_input_string, 1) - 1) as output_value,
		substring(@v_input_string, charindex(@v_delimiter, @v_input_string, 1) + 1, len(@v_input_string)) + @v_delimiter as list
	union all
	select 
		substring(list, 1, charindex(@v_delimiter, list, 1) -1) as output_value,
		substring(list, charindex(@v_delimiter, list, 1) + 1, len(list)) as list
	from v_go_loop
	where len(v_go_loop.list) > 0
)
Select output_value From v_go_loop;

執行Demo Code會發生如下圖的錯誤訊息。顯示錨點跟遞迴的資料類型不符。

將@v_input_string改宣告成varchar(8000)或varchar(max)後，則語法可以順利執行，如下兩圖

使用SQL_VARIANT_PROPERTY函數，我們來檢視錨點的output_value及list欄位資料長度是多少。

declare @v_input_string varchar(1000)='A;B;C';
declare @v_delimiter char(1)=';';

with v_go_loop (output_value, list) as
(
	select 
		substring(@v_input_string, 1, charindex(@v_delimiter, @v_input_string, 1) - 1) as output_value,
		substring(@v_input_string, charindex(@v_delimiter, @v_input_string, 1) + 1, len(@v_input_string)) + @v_delimiter as list
	--union all
	--select 
	--	substring(list, 1, charindex(@v_delimiter, list, 1) -1) as output_value,
	--	substring(list, charindex(@v_delimiter, list, 1) + 1, len(list)) as list
	--from v_go_loop
	--where len(v_go_loop.list) > 0
)
Select 
SQL_VARIANT_PROPERTY(output_value,'MaxLength') as output_value_length,
SQL_VARIANT_PROPERTY(list,'MaxLength') as list_length
from v_go_loop

當我們將@v_input_string宣告為varchar(1000)時。

由substring(@v_input_string, 1, charindex(@v_delimiter, @v_input_string, 1) - 1)產出的output_value欄位長度是1000。

由substring(@v_input_string, charindex(@v_delimiter, @v_input_string, 1) + 1, len(@v_input_string)) + @v_delimiter產出的list欄位長度會變成1001(因為varchar(1000)+char(1)=1001)。

因為遞迴部分的output_value欄位會利用錨點的list欄位來SubString，因此會導致錨點的output_value欄位長度是1000，而遞迴的output_value欄位長度是1001，因此發生錯誤。

而當我們將@v_input_string宣告為varchar(8000)時。
則output_value欄位跟list欄位的長度都是8000。因為varchar最大長度就是8000，因此在怎麼做字串相加，他還是8000，varchar(max)也一樣，如下圖。

本範例語法可以改成下面語法，即可避開錯誤。

declare @v_input_string varchar(1000)='A;B;C';
declare @v_delimiter char(1)=';';
set @v_input_string = @v_input_string + @v_delimiter;
with v_go_loop (output_value, list) as
(
	select 
		substring(@v_input_string, 1, charindex(@v_delimiter, @v_input_string, 1) - 1) as output_value,
		substring(@v_input_string, charindex(@v_delimiter, @v_input_string, 1) + 1, len(@v_input_string)) as list
	union all
	select 
		substring(list, 1, charindex(@v_delimiter, list, 1) -1) as output_value,
		substring(list, charindex(@v_delimiter, list, 1) + 1, len(list)) as list
	from v_go_loop
	where len(v_go_loop.list) > 0
)
Select output_value From v_go_loop;