Database

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[fn_EmpRec]

(

    @TemplatesID int,
	@depth int

)

RETURNS	@Templates TABLE

(

      TemplatesID	bigint,

      [Name]	nvarchar(50),

      ParentID	bigint,

      depth	int, 

	  Weight	float

	 

)


AS

BEGIN

      -- insert current Template into working table

      INSERT INTO @Templates

            SELECT TemplatesID,

                     [Name],

                     ParentID,

                     @depth 

		    ,Weight
					
            FROM [Assessment].[Template] 

            WHERE TemplatesID = @TemplatesID

      -- holding variable to keep track of current child 

      DECLARE @curTemplatesID int

      -- get the first child

      SELECT @curTemplatesID = MIN(TemplatesID) 

      FROM [Assessment].[Template]

      WHERE ParentID = @TemplatesID

      

      -- iterate each child and make the recursive call

      WHILE @curTemplatesID IS NOT NULL

      BEGIN

            INSERT INTO @Templates

                  SELECT * 

                  FROM dbo.fn_EmpRec(@curTemplatesID, @depth + 1)

                  

            SELECT @curTemplatesID = MIN(TemplatesID) 

                  FROM [Assessment].[Template]

                  WHERE TemplatesID > @curTemplatesID AND

                          ParentID = @TemplatesID

      END

      RETURN

END