Database

CREATE FUNCTION dbo.GetModule
{
       @ListID nvarchar(2000)
}

<code>--Concatenate all ChildIDs of all IDs in @ListID. Basically COALESCE is used to replace NULL with '' (empty string)</code>
DECLARE @ChildIDs varchar(100);
SELECT @ChildIDs = COALESCE (@ChildIDs + ', ', '') + ChildIDs          
FROM tblModule WHERE ID IN (SELECT value FROM dbo.Split(@ListID)))

<code>-- Selects the Parent's ID and ModuleID, then UNION with ChildIDs' result if there are ChildIDs</code>
IF @ChildIDs = null

  SELECT ID, ModuleID                                     --This is to return only parent result if there are no ChildIDs
  FROM tblModule 
  WHERE ID IN (SELECT value FROM dbo.Split(@ListID)

ELSE

  SELECT ID, ModuleID
  FROM tblModule 
  WHERE ID IN (SELECT value FROM dbo.Split(@ListID)

  UNION                                                    --This is the recursive code to union with result of ChildIDs

  SELECT ID, ModuleID
  FROM dbo.GetModule(@ChildIDs)

RETURN

CREATE FUNCTION dbo.Split
(
	@List nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
		
	Id int identity(1,1),
	Value nvarchar(100)
) 
AS  
BEGIN
	WHILE (CHARINDEX(@SplitOn,@List)>0)
	BEGIN 
	
	INSERT INTO @RtnValue (value)
	SELECT 
	    Value = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1))) 
	
	   Set @List = SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List))
	END
	
	INSERT INTO @RtnValue (Value)
	SELECT Value = LTRIM(RTRIM(@List))
	
	RETURN
END

Id |  Value
 1     1
 2     2
 3     3
 4     4

CREATE FUNCTION dbo.GetModule
{
       @ListID nvarchar(2000)
}

DECLARE @ChildIDs varchar(100);

--Concatenate all ChildIDs of all IDs in @ListID. Basically COALESCE is used to replace NULL with '' (empty string)
SELECT @ChildIDs = COALESCE (@ChildIDs + ', ', '') + ChildIDs          
FROM tblModule WHERE ID IN (SELECT value FROM dbo.Split(@ListID)))

-- Selects the Parent's ID and ModuleID, then UNION with ChildIDs' result if there are ChildIDs
IF @ChildIDs = null

  SELECT ID, ModuleID                                     --This is to return only parent result if there are no ChildIDs
  FROM tblModule 
  WHERE ID IN (SELECT value FROM dbo.Split(@ListID)

ELSE

  SELECT ID, ModuleID
  FROM tblModule 
  WHERE ID IN (SELECT value FROM dbo.Split(@ListID)

  UNION                                                    --This is the recursive code to union with result of ChildIDs

  SELECT ID, ModuleID
  FROM dbo.GetModule(@ChildIDs)