recursive user defined function

[enas]

i'm trying to write function that deals with a recursive table called "centers" with coloms "center_id PK,Parent_id FK to center_id,center" as i want to get the parent and all his childs but i have problems this is my function CREATE FUNCTION Full_Center_Path(@IN_CAT_ID int) RETURNS nvarchar AS BEGIN DECLARE @OUT_PATH nvarchar DECLARE @center nVARCHAR DECLARE @center_ID int

EXEC SQL SELECT max(Centers.center),MAX(Centers.PARENT_ID) into :center,:center_ID FROM centers WHERE centers.center_ID=@IN_CAT_ID;

iF IN_CAT_ID <>0 set @OUT_PATH=Full_Center_Path(Center_ID) + center+'/'

RETURN (@OUT_PATH); END can any one help me in this