SQL recursive query subtype

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [f_getchildcate] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [f_getchildcate]
Go

Set quoted_identifier off
Go
Set ansi_nulls off
Go

Create Function f_getchildcate (@ id int)
Returns @ child table ([cateid] int, catename nvarchar (127), parentid INT)
As
Begin

Begin
Insert @ child select ID, c_catename, c_parentid from [t_cate] Where [c_parentid] = @ ID
While @ rowcount> 0
Begin
Insert @ child select B. ID, B .C _ catename, B .C _ parentid from @ child
Inner join [t_cate] B on A. [cateid] = B. [c_parentid]
Where B. [c_parentid] Not in (select distinct [parentid] From @ child)
End
End

Return
End

Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go