Recursive Function does not work

1

I'm new to SQL. I tried to create a recursive function:

FUNCTION [dbo].[parts_hierarchy] (@partId UNIQUEIDENTIFIER)
RETURNS TABLE
AS
RETURN
(
    WITH cte_part AS (
    SELECT  [PartId], [PartName], [ParentPartId]  FROM  [dbo].[Parts]   WHERE [ParentPartId] IS NULL
    UNION ALL
    SELECT  e.[PartId], e.[PartName], e.[ParentPartId]  FROM [dbo].[Parts] e INNER JOIN cte_part o  ON o.[PartId] = e.[ParentPartId]
	)
	SELECT * FROM cte_part;
);

The following error is shown:

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'FUNCTION'.
Msg 319, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.

2

create should be there before function

create FUNCTION [dbo].[parts_hierarchy] (@partId UNIQUEIDENTIFIER)

1 Like