Sql Server 递归

declare @FatherCode varchar(50)='001';


if object_id('tempdb..#Temp') is not null
begin
drop table #Temp
end
;
declare @tmp TABLE(
TypeCode varchar(50),
FatherCode varchar(50)
);

WITH cteTypeCode AS
(
    SELECT a.TypeCode,FatherCode FROM T_ItemType a WHERE a.FatherCode=@FatherCode
    UNION ALL
    SELECT a.TypeCode,a.FatherCode FROM T_ItemType a INNER JOIN cteTypeCode c ON a.FatherCode =c.TypeCode
)

--SELECT * INTO #Temp FROM cteTypeCode
INSERT INTO @tmp select * FROM cteTypeCode
--select * from #Temp

select * from @tmp